I have a mail list that I need to merge the duplicate addresses together and concatenate the names. Example below:

Name        Street_Address
---------   ----------------
JohnDoe     444 Elm Street
JaneDoe     444 Elm Street
BillyBob    456 Birch Street
BugsBunny   999 Maple Street
BabsBunny   999 Maple Street

I would like to merge the duplicates like so:

Name                 Street_Address
------------------   ----------------
JohnDoeJaneDoe       444 Elm Street
BillyBob             456 Birch Street
BugsBunnyBabsBunny   999 Maple Street

I've tried using the consolidate feature but you don't get the option for concatenate. Thanks for your help!

  • In the source Excel file that you have, are all of the duplicate addresses grouped together, or are they spread out through the document? – JamesTheDev Aug 21 '13 at 20:53
  • They are spread out, but I can sort by address if that helps. – Ryan Janis Aug 21 '13 at 21:01

If your addresses are exactly identical, and if you sort based on the addresses, and if this is a one-time process, you can use the following formulas.

Assuming Name is column A and Street_Address is column B, and that you have labels in row 1:

Column C will hold the following formula (example from C2): =IF(B2=B1,CONCATENATE(C1,A2),A2)

Column D will hold the following formula (example from D2): =IF(B2=B3,"DeleteMe","KeepMe")

After you've copied the formulas to all rows, copy and then paste as values in the same spot.

Now, just use filtering to only see the "DeleteMe" rows, and delete them all.

  • That's easy enough! Hopefully it is a one time thing, only time will tell. Thanks! – Ryan Janis Aug 22 '13 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.