Compare cells and concatenate on match
 

I need to compare rows in a table, and concatenate data in col D
wherever there's a match to previous rows in cols A:C. The number of
rows where matches may occur is variable. After the data is
concatenated, the extraneous matching rows must be deleted.

A B C D
Europe Germany Name1 Frankfurt
Europe Germany Name1 Munich
Europe Germany Name1 Bonn
Europe Germany Name1 Dusseldorf
Europe France Name2 Paris
North America United States Name3 Chicago
North America United States Name4 Seattle
Europe United Kingdom Name4 London
Asia Japan Name5 Tokyo
Asia Japan Name5 Osaka
Asia Japan Name5 Kyoto

Desire output as follows:

A B C D
Europe Germany Name1 Frankfurt, Munich, Bonn, Dusseldorf
Europe France Name2 Paris
North America United States Name3 Chicago
North America United States Name4 Seattle
Europe United Kingdom Name4 London
Asia Japan Name5 Tokyo, Osaka, Kyoto

I can do part of what I need with this IF statement:
=IF(A3=A2,IF(B3=B2,IF(C3=C2,CONCATENATE(D3&", "&D2),D2)))

I can't figure out how to address more than 2 rows that match or how
to delete the extra rows. I have made numerous attempts but am stumped.
Will this need to be done via VBA? If you can point me to any relevant
resources for a coding jump-start, that'd be great. Thanks for any
inspiration on this!

Compare cells and concatenate on match
 

This did the trick beautifully. Thank you so much!

Compare cells and concatenate on match
 

I'm using this code of Martins with numbers instead of text and I would
like it to output ONLY the largest value in column 4, instead of a
concatenation of all the values. I'm sure it's simple but I can't seem
to figure it out.

Thanks,
-- Dan

Martin Fishlock wrote:
Hi,

Try this:

Option Explicit

Sub concat()

Dim lRowFirst As Long
Dim lRowLast As Long
Dim lRow As Long
lRowFirst = ActiveCell.CurrentRegion.Row + 1 '
lRowLast = ActiveCell.CurrentRegion.Rows.Count + lRowFirst - 2

For lRow = lRowLast To lRowFirst Step -1
If Cells(lRow, 1) = Cells(lRow - 1, 1) And _
Cells(lRow, 2) = Cells(lRow - 1, 2) And _
Cells(lRow, 3) = Cells(lRow - 1, 3) Then
Cells(lRow - 1, 4) = Cells(lRow - 1, 4) _
& ", " & Cells(lRow, 4)
Rows(lRow).Delete
End If
Next lRow

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


" wrote:

I need to compare rows in a table, and concatenate data in col D
wherever there's a match to previous rows in cols A:C. The number of
rows where matches may occur is variable. After the data is
concatenated, the extraneous matching rows must be deleted.

A B C D
Europe Germany Name1 Frankfurt
Europe Germany Name1 Munich
Europe Germany Name1 Bonn
Europe Germany Name1 Dusseldorf
Europe France Name2 Paris
North America United States Name3 Chicago
North America United States Name4 Seattle
Europe United Kingdom Name4 London
Asia Japan Name5 Tokyo
Asia Japan Name5 Osaka
Asia Japan Name5 Kyoto

Desire output as follows:

A B C D
Europe Germany Name1 Frankfurt, Munich, Bonn, Dusseldorf
Europe France Name2 Paris
North America United States Name3 Chicago
North America United States Name4 Seattle
Europe United Kingdom Name4 London
Asia Japan Name5 Tokyo, Osaka, Kyoto

I can do part of what I need with this IF statement:
=IF(A3=A2,IF(B3=B2,IF(C3=C2,CONCATENATE(D3&", "&D2),D2)))

I can't figure out how to address more than 2 rows that match or how
to delete the extra rows. I have made numerous attempts but am stumped.
Will this need to be done via VBA? If you can point me to any relevant
resources for a coding jump-start, that'd be great. Thanks for any
inspiration on this!