Combining info for mailing labels

[Laura D.]

I have a basic spreadsheet with contact information. The way it is set up is
that everyone has their own entry, even if there are several people living in
the same address.

How do I set up my spreadsheet so that it amalgomates all names in the same
household? I want to sent one Christmas card per household and hopefully
address it to all people within that home.

My colums are First Name, Last Name, Address, City, Postal Code. I have
30000 names in this list so I am hoping Excel can do some of the footwork.

 

[Paul C]

First sort your data by Address, City and Postal Code
Delete any blank rows at the top

Next insert a column between Last name and Address
Put in a formula to merge the first and last name
=concatenate(A1," ",B1)
this will take
A B
John Smith

and make
A B C
John Smith John Smith

then you can use a small macro to combine. Copy the sheet so you are not
working on your original data just to be safe.

Sub mergenames()
Range("C1").Select
Do While ActiveCell <> Empty
'MATCH ADDRESS, CITY AND POSTAL CODE
If ActiveCell.Offset(0, 1) = ActiveCell.Offset(1, 1) And _
ActiveCell.Offset(0, 2) = ActiveCell.Offset(1, 2) And _
ActiveCell.Offset(0, 3) = ActiveCell.Offset(1, 3) Then
'COMBINE NAME AND DELETE
ActiveCell = ActiveCell & ", " & ActiveCell.Offset(1, 0)
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub