How to move cells into another cell

  • Thread starter Thread starter josev
  • Start date Start date
J

josev

Hello,

I hope someone can help me on this. I am trying to move cells from
group into a top cell, the initial view of column A (very simplistic
of the cell looks like this:

1
2
3
4

1
2
2
2

3
3
3

1

1
2
3

What I want to do is to move 2 3 4 from the first group to the cel
that contains the 1 (first entry) and continue with the rest of th
cells so at the end columnA will look like this:

1 2 3 4
1 2 2 2
333
1
123

The cells vary in lenght and I have several spreasheets with LARG
amounts of data that I have to processed, so I am hoping that there i
a macro (VB) that is able to do that as I am a novice on VB

Cheers!

-Jose

P.s., each group has a blank row in between
 
Hi
you may try the following macro:
- select the column/cells to be processes
- the macro will place the return values in the adjacent column

-----
Sub move_numbers()
Dim rng As Range
Dim cell As Range
Dim row_index As Long
Dim col_index As Integer
Dim ret_string As String

Set rng = Selection
If rng.Columns.Count > 1 Then
MsgBox "Only one column allowed"
Exit Sub
End If

col_index = rng.Column + 1
row_index = rng.row
ret_string = ""

For Each cell In rng
If cell.Value = 1 Then
If ret_string <> "" Then
Cells(row_index, col_index).Value = ret_string
row_index = row_index + 1
End If
ret_string = "1"
ElseIf cell.Value <> "" Then
ret_string = ret_string & " " & cell.Value
End If
Next
Cells(row_index, col_index).Value = ret_string
End Sub
 
Hi Frank,

Thank you for your reply. I run the macro that you posted and I go
somewhat of mix results. This is how column A and B looks like now:

1 1 2 3 4
2 1 2 2 2 3 3 3
3 1
4 1 2 3

1
2
2
2

3
3
3

1

1
2
3

Column A and B should look like this:

1 1 2 3 4
2
3
4

1 1 2 2 2
2
2
2

3 333
3
3

1 1

1 1 2 3
2
3

Any help would be greatly appreciated. I know that I am pushing here s
my apologies in advance.

Regards

-Jos
 
Hi
not quite sure if this is what you want but give it a try:
- A blank row is used as delimiter
- all combined strings are put in column A (without blank rows in
between) -> I added a comment how to change the respective line to
align the output to column A's entries

Sub move_numbers()
Dim rng As Range
Dim cell As Range
Dim row_index As Long
Dim col_index As Integer
Dim ret_string As String

Set rng = Selection
If rng.Columns.Count > 1 Then
MsgBox "Only one column allowed"
Exit Sub
End If

col_index = rng.Column + 1
row_index = rng.row
ret_string = ""

For Each cell In rng
If cell.Value = "" Then
If ret_string <> "" Then
Cells(row_index, col_index).Value = ret_string
row_index = row_index + 1
'row_index=cell.row+1 'use this for aligned output to
column A
End If
ret_string = ""
ElseIf cell.Value <> "" Then
ret_string = ret_string & " " & cell.Value
End If
Next
Cells(row_index, col_index).Value = ret_string
End Sub
 
Hi Frank,

Thank you VERY much for the updated macro, it worked like a charm
there were some odd results but they were not the macros fault
remember that I mentioned that there was a blank line in between eac
group ( and you set a delimeter to be a blank), well it turns out tha
that there were groups that had 2, 3 4, even 5 blank in between.
correct them manually since there were about 30 intries like that, i
beats correcting about 4,000 groups total. .

Orce again, thank you very much for your help

Best Regards,

-Jos
 
Back
Top