Martin Fishlock
12/18/2006 11:47:00 PM
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.
"vs88@yahoo.com" 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!
>
>