Help: Removing Duplicate Rows + Concatenating

[Carlitozway]

Hello,

First let me say that I am a beginner in Excel and stumped by a really important task. I have to find all duplicate records in 48,000+ rows and then concetenate values from a single column.

I have 10 columns of data and want to find all duplicates by Email Address and then concatenate values within Text 1 and Text 2.

-First Name
-Last Name
-Address
-City
-Province
-Postal Code
-Phone Number
-Email Address
-Text 1
-Text 2

Can anyone tell me the cleanest way to do so as trying this manually will take ages? Thanks in advance for the help.

Here is a sample of the data.

<table width="1011" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:5266;width:108pt" width="144"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:6838;width:140pt" width="187"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:56pt" height="20" width="74">First Name</td> <td class="xl64" style="width:53pt" width="71">Last Name</td> <td class="xl64" style="width:108pt" width="144">Address</td> <td class="xl64" style="width:51pt" width="68">City</td> <td class="xl64" style="width:46pt" width="61">Province</td> <td class="xl64" style="width:60pt" width="80">Postal Code</td> <td class="xl64" style="width:77pt" width="102">Phone Number</td> <td class="xl64" style="width:140pt" width="187">Email Address</td> <td class="xl64" style="width:98pt" width="131">Text 1</td> <td class="xl64" style="width:70pt" width="93">Text 2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">Nick</td> <td class="xl64">Smith</td> <td class="xl64">71 Niagara Avenue</td> <td class="xl64">Toronto</td> <td class="xl64">ON</td> <td class="xl64">M5V1C4</td> <td class="xl64">6458014380</td> <td class="xl65">nick@email.com</td> <td class="xl64">26856081, 15635679 </td> <td class="xl64">464351</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">Bonnie</td> <td class="xl64">Hamil</td> <td class="xl64">Comp. 2, Site 13, RR#2</td> <td class="xl64">Edmonton</td> <td class="xl64">AB</td> <td class="xl64">T0E0H1</td> <td class="xl64">780-968-5070</td> <td class="xl65">gabole@telus.ca</td> <td class="xl64">26856081, 15635679 </td> <td class="xl64">464351</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">Nick</td> <td class="xl64">Smith</td> <td class="xl64">71 Niagara Ave</td> <td class="xl64">Toronto</td> <td class="xl64">ON</td> <td class="xl64">M5V1C4</td> <td class="xl64">645-801-4380</td> <td class="xl65">nick@email.com</td> <td class="xl64">26856081, 15635680</td> <td class="xl64">464352</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">Cyndi</td> <td class="xl64">Thomas</td> <td class="xl64">83 Laurel Street</td> <td class="xl64">Etobicoke</td> <td class="xl64">ON</td> <td class="xl64">M9B4T7</td> <td class="xl64">4152340384</td> <td class="xl65">c.thomas@rogers.ca</td> <td class="xl64">26856081, 15635679 </td> <td class="xl64">464351</td> </tr> </tbody></table>

 

[konew1]

What version of excel do you have? 2010 has "remove duplicates" on the data ribbon. I think 2007 has it.

 

[Carlitozway]

I am using Excel 2007. Removing duplicates on its own I can do, but the concatenating of values in Text 1 and Text 2 columns for those duplicates into a unique row is the challenging part for me. Any idea how to?

 

[konew1]

Looking at your data and I see the Text1 and text2 are different on the two lines for Nick Smith. Does it matter which line is deleted?
I hope you have pasted demo data and not clients addresses and email contacts

 

[konew1]

What do you mean by concatenate text1 and text2? if the data is in columns I and J then =I2&J2 will do it, then copy down.

 

[Carlitozway]

No as long as I can have a unique row per Email Address with the values in Text 1 and Text concatenated, I will be fine.

Yes I was careful to use sample data rather than actual data. Thanks for asking.

Do you know how I can rationalize this spreadsheet's rows?

 

[Carlitozway]

What do you mean by concatenate text1 and text2? if the data is in columns I and J then =I2&J2 will do it, then copy down.

I am trying to combine the values within Text 1 column and in Text 2 separately after finding duplicate rows using the Email Address.

In the sample, I would like to have one cell in Text 1 column and one cell in Text 2 column that has comma separated values from duplicate rows based on matching email addresses, "nick@email.ca".

 

[konew1]

Please confirm: You want to concatenate the text1 data from the duplicate entries into a single cell for one line with that email address, then the same for text2, then delete the rows where the data came from so there is only one line for each email address, and all of the text 1 and text2 data is retained. Is that correct?
It can be done by formula, but not easy if there can be multiple entries for each email. I think you need code - someone else will need help with that