0

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!

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

1 Answer 1

4

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .