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!

This did the trick beautifully. Thank you so much!

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.
>
>
> "(E-Mail Removed)" 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!
> >
> >