V
vs88
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!
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!