Compare cells and concatenate on match


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!
 
Ad

Advertisements

Ad

Advertisements

D

Dan R.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top