Merging rows based on duplicate values in another column

Question

Hi,

I want to merge rows of column B of duplicate values in column A.

1234	205.11y
1234	569.44yu
1234	214.6kl
1234	205.11yh
1895	564.34dy
1895	564.37uc
1895	456.9ai
5894	589.36it
5894	549.87yui
6349	205.11yh
6349	5697.25fv
6349	459.6opr
6349	582.39yw

 As shown above, I would like to merge Column B for every duplicate value in column A. The best I could come up with is using a merge of an IF and a CONCATENATE function. But that is limiting to like number of rows.

Answer 1

Copy the code for the ConcatenateIf function from http://eileenslounge.com/viewtopic.php?p=91187#p91187 into a module in the Visual Basic Editor.

You can then use the following formula in C1:

=ConcatenateIf($A$1:$A$13,$A1,$B$1:$B$13)

and fill down. Adjust the ranges as needed.

Alternatively:

In C1, enter =B1.

In C2, enter =IF(B2=B1,C1&B2,B2)

Fill down from C2 to the end of the data. The last entry for each unique value in column A will contain the merged value.

Answer 2

Excel 2010 with free Power Query Add-In.

Compatible with Office 2013 Pro Plus.

No formulas, no VBA macro.

Dynamic.

http://www.mediafire.com/view/umpbm05n6lab9a9/07_25_15.xlsx

Answer 3

Thank you Hans,

This is real simple to implement and I was looking for something on this exact level only.

Thanks again.

Have a nice day.

Mercina

Answer 4

Dear Ashish,

Thank you for the solution. But unfortunately I cannot enable Power Query, as it needs to be installed which won't happen without a good reason on our company computers.

Anyways, I will definitely explore the options in Power Query on my personal computer. Thanks again for helping.

Mercina.

Answer 5

Hi,

You may refer to my solution at this link - http://www.ashishmathur.com/merge-data-from-multiple-cells-into-a-single-cell/.

Hope this helps.