concatenate cells with repeated information

[mavi99]

Hi guys,

I have a small problem regarding my excel worksheet. It has two columns:
In one column (A) there are repeated cells. I want to concatenate the information on the second column cells (B), if the first column (A) has repeated cells and finally reach Column C. Which formula(s) need to be used in order to create Column C? Would you help me with that?

Presentation1.jpg

Thanks a lot in advance!

Onur

[Sam Capricci]

I can get you there but it would take some helper columns, are you open to that?
Plus, how far out do these markets go for one individual. My solution would require one column for each market value for a person, so for Steve his results would go out to column G, if you have someone with 8 markets that would take it out to column K. You'll see in my sample workbook. And you'd have to add to the summary formula for add'l covered areas beyond column G.

BTW, here are the formulas.
Cell D2 dragged right then down... =IF($A1=$A2,"",IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,ROW($1:$10)/($A$2:$A$9=$A2)/($B$2:$B$9<>""),COLUMNS($A:A))),""))
Cell C2 dragged down... =IF(AND(D2<>"",E2=""),D2,IF(AND(D2<>"",E2<>"",F2=""),D2&"; "&E2,IF(AND(D2<>"",E2<>"",F2<>"",G2=""),D2&"; "&E2&"; "&F2,IF(AND(D2<>"",E2<>"",F2<>"",G2<>"",H2=""),D2&"; "&E2&"; "&F2&"; "&G2,""))))

[mavi99]

Thanks a lot Sambo.
Indeed, my original dataset has thousands of lines, and there are more than 50 individual "markets". So, opening a column for each individual market couldn't be the best idea. But if there are no other solutions, I guess we need to follow this.

[Sam Capricci]

Well, you could start the helper columns out of the area they could be visible, say in column BA for example but the if statement in column C would be quite long to handle up to 50 individual markets AND the names would all have to be combined (not out of order) because if you had John again below Steve you'd get an additional summary line across from him again.
It almost sounds like you might need a VBA solution but I'll be interested in seeing what else is proposed.