Excel: Transpose row data into list

[wayneryeven]

Hi all,
Hoping someone can assist..

I have a list of data:

City | Person

New York | David
New York | Pamela
LA | Colin

What i want to do is group all the persons from the same city into a list against that city name:

New York | David, Pamela
LA | Colin

In the second column i just want the persons name seperated with a comma. Unfortuantely there isnt the same number of persons in each city...

Hoping someone can point me in the right direction!

Thanks
wdv

 

[GlennUK]

Is the list sorted so that all the cities are grouped together?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lavaghman]

Have exactly the same thing to do and for me yes the City column is in order.

Tried using a concatenate combined with an if statement and it gets what i want in the last row for each city but then I can't isolate all those last rows.

No good if I have a 1000 rows!

Cheers.

 

[lavaghman]

Got it sorted using a Macro, as follows:

this macro should work. It assumes:
•your cities list (City A, City B etc) is in a column with the range name "city_range"
•this column is sorted so all instances of eg "new York" appear in sequence one below the other
•your names are in the adjacent column to the right
•there is a cell named "destination_cell" (actually named as a range) for the first line of combined text. Subsequent lines will be input below


CODE:
Sub merge_text()
Dim city_range As Range, Destination_cell As Range, SourceCell As Range, This_City As String, DestNum As Long, txt As String
This_City = ""
txt = ""
DestNum = 0
For Each SourceCell In Range("city_range").Cells
If SourceCell.Value <> This_City Then
Range("Destination_cell").Offset(DestNum, 0).Value = txt
If txt <> "" Then DestNum = 1 + DestNum
This_City = SourceCell.Value
txt = This_City & " | " & SourceCell.Offset(0, 1).Value
Else
txt = txt & " / " & SourceCell.Offset(0, 1).Value
End If
Next
Range("Destination_cell").Offset(DestNum, 0).Value = txt
End Sub

 

[wayneryeven]

Hey lavaghman
THanks for the reply- code looks good and i will test later. To get away yesterday i did a somewhat unconventional approach of concatenating entries if the previous city was the same, i then added a flag to each row which indicated if it was the last record, filtered this and lifted my data out.

As i say unconvential but time was a factor, and it get the task done. Certainly a more robust structured macro is the way forward, of which you have offered.

I will test this- many thanks!

 

[lavaghman]

Hi wayner1980

I started with a similiar aproach myself. When you say you added a flag, did you do this manually or did you find a way of isolating that last row for each city (that's where I got stuck with this approach)?

Cheers.