Concatenating values from a single column to remove duplicates in another.

**Mumps** · 06-06-2012, 12:22 PM

Hello,

I am trying to combine columns so I can get rid of the duplicate values in COLUMNA, and concatenate the corresponding values in COLUMNB with a "; " separating them. I was wondering what kind of formula I would need to do so.

What I have:

Please Login or Register  to view this content.

What I need the final result to be:

Please Login or Register  to view this content.

I have a worksheet of over 1,000 values, and about half of them contain duplicate COLUMNA values.

I hope I was descriptive enough, let me know if you have questions regarding my examples.

Thank you!

**tigeravatar** · 06-06-2012, 01:41 PM

Mumps,

Unfortunately, Excel's native CONCATENATE() function doens't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in. I recommend the Morefunc add-in which provides the MCONCAT function which is designed for exactly this task.

**DJ JR** · 06-06-2012, 02:05 PM

Paste in cell C2 and drag down, assuming column A is sorted in ascending order:

=IF(A2=A1,CONCATENATE(C1,"; ",B2),B2)

Best

**tigeravatar** · 06-06-2012, 02:14 PM

DJ JR's suggestion will result in the following:

Please Login or Register  to view this content.

If that's what you are looking for, then it is a good solution. It does require that column A be sorted (as he already pointed out).

**Mumps** · 06-06-2012, 02:15 PM

Thanks a bunch, you have solved my problem. After this I was just able to isolate the values and delete my duplicates by using the longest string of values in relation to column A.

My day is saved :D