Merge data from separate columns

[blueflyer]

Dear gurus,

We've been trying here to fight an issue where we can't get to a solution.

We have a table like:

A abrf
B bdkw
C jfgd
A jfdk
E jkfg
B roer

(where the single char is column A and the 4 letters are column B)

The goal was to have in column C the merger of the values in column B for which the value in column A is the same, like:

abrf-jfdk
bdkw-roer
jfgd
abrf-jfdk
jkfg
bdkw-roer

Can someone give us a hand?

Highly appreciated.

[Blake 7]

one way may be to filter a - z on col a and put this in col c

=IF(A2=A1,B1&" "&B2,B2)

[blueflyer]

one way may be to filter a - z on col a and put this in col c

=IF(A2=A1,B1&" "&B2,B2)

Hi Blake,

Thank you for your answer.
Nevertheless, the expectation is that the formula can process n number of similar values in column A, where n is variable.

Thanks!

[Blake 7]

ok - i'll take another look. but could you say if col a will be txt, num or mixture, will there be any dashes?

[blueflyer]

ok - i'll take another look. but could you say if col a will be txt, num or mixture, will there be any dashes?

Hello,
It's email addresses.

[ChemistB]

Okay, no one said this would be easy.
First I created a unique list of values from Column A in Column D starting in D1.
Then starting in F1 and dragging to the right and then down,

=IF(COLUMNS($A$1:A1)>COUNTIF($A$1:$A$500,$D1),"",INDEX($B$1:$B$500,SMALL(($A$1:$A$500=$D1)*ROW($A$1:$A$500),COLUMNS($A$1:A$1)+COUNTIF($A$1:$A$500,"<>"&$D1)))) entered as an array (CNTRL SHFT ENTER instead of ENTER)

That puts all values associated with A in cells in the same row as A (see attachment)

Now you need to concatenate with dashes. You could do this manually, i.e. in E1
=F1&"-"&G1&"-"&H1 but that's time consuming.

Here's a User Defined Function (UDF)

Please Login or Register  to view this content.

Then in E1
=IF(ISBLANK(D1),"",Concat(F1:O1) dragged down.
How we doing?