Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
    
In the source Excel file that you have, are all of the duplicate addresses grouped together, or are they spread out through the document? –  NewProgrammerJames 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
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
That's easy enough! Hopefully it is a one time thing, only time will tell. Thanks! –  Ryan Janis Aug 22 '13 at 12:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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