Concatenate, but remove duplicates

[dolfin]

So I'm new around these parts, and after browsing/searching for a bit I'm convinced someone here can figure this out


I'm trying to prepare an upload to my online system, and need to concatenate fields to a single Record ID. Problem, there can be (and is) several duplicates, but I need unique values.

Here's the data:

13661	Credit |  Funds | EU
13661	Buyout | Funds |EU
13661	Buyout | Funds | EU
13661	Growth | Buyout | Funds | EU 
13680	Distressed | Funds |US
13688	Buyout | Funds | EU
13689	Credit | Funds | EU
13689	Buyout | Funds | EU

Here's what I'd like to end up with (notice the single row for each ID#):

13661	Buyout | Growth | Credit |  Funds | EU
13680	Distressed | Funds |US
13688	Buyout | Funds | EU
13689	Buyout | Credit | Funds | EU

 

[drgracer]

What version of Excel are you using? Excel 2007/2010 has a Remove Duplicates function in the Data Ribbon

 

[dolfin]

Version 2007, but I need the duplicate entries to removed from the second column. Sorry, I realize I wasn't terribly clear. I basically need to consolidate the rows thy have the same record id (first column).

 

[shawnhet]

There's probably a simpler way to do this, but this should work - just change the following for the right ranges.

From C1 to J1 respectively

=IF(SUMPRODUCT(--($A$1:$A$8=A$1)*((ISNUMBER(FIND("Buyout",$B$1:$B$8)))))>0," Buyout |","")
=IF(SUMPRODUCT(--($A$1:$A$8=$A1)*((ISNUMBER(FIND("Credit",$B$1:$B$8)))))>0," Credit |","")
=IF(SUMPRODUCT(--($A$1:$A$8=$A1)*((ISNUMBER(FIND("Distressed",$B$1:$B$8)))))>0," Distressed |","")
=IF(SUMPRODUCT(--($A$1:$A$8=$A1)*((ISNUMBER(FIND("Funds",$B$1:$B$8)))))>0," Funds |","")
=IF(SUMPRODUCT(--($A$1:$A$8=$A1)*((ISNUMBER(FIND("Growth",$B$1:$B$8)))))>0," Growth |","")
=IF(SUMPRODUCT(--($A$1:$A$8=$A1)*((ISNUMBER(FIND("EU",$B$1:$B$8)))))>0," EU |","")
=IF(SUMPRODUCT(--($A$1:$A$8=$A1)*((ISNUMBER(FIND("US",$B$1:$B$8)))))>0," US |","")
=CONCATENATE(C1,D1,E1,F1,G1,H1,I1)

Cheers,

 

[dolfin]

Thanks!! That worked great!! Much appreciated!!

 

[shivneetns]

Hi, i am working as an NGO

need to create excel where if duplicate data is found
1. It will remove that COLUMN.
2. Duplication of email and phone need to check.
3. If found duplicate concatenate like column A value, Column B value concatenate in first occurrence like Ax(Bx), then next duplicate value concatenate.

I need all the columns of an excel will do this without choosing a particular limited column,Row value.

Having data in huge quantity...

There's probably a simpler way to do this, but this should work - just change the following for the right ranges.

From C1 to J1 respectively



Cheers,