Tuesday, September 25, 2018

concatenation - Excel Concatenate Rows



I have this excel worksheet



A          B          c   
foo1 joo1 loo1

foo1 joo2 loo2
foo2 joo3 loo3
foo2 joo4 loo4
foo2 joo5 loo5


Now I want this



A          B                  c   
foo1 joo1, joo2 loo1, loo2

foo2 joo3, joo4, joo5 loo3, loo4, loo5


How do I do this, preferably with GUI?


Answer



If you are willing to write a macro...



Click the first "foo1" cell and run this. It will join the foo1's then the foo2's until it hits a blank cell. If you've never written a macro before perhaps we can walk you thru it.





Sub JoinThem()
' Go until blank cell (first click at upper left of range)
Do While ActiveCell.Value <> ""
' If this row macthes the next row
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
' Join first column overwriting the values
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1).Value & ", " & ActiveCell.Offset(1, 1).Value
' and Join the second column too
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2).Value & ", " & ActiveCell.Offset(1, 2).Value
' Since we placed the data into one row get rid of the row

ActiveCell.Offset(1, 0).EntireRow.Delete
Else
' Next row does not match, move down one
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

No comments:

Post a Comment

plot explanation - Why did Peaches&#39; mom hang on the tree? - Movies &amp; TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...