Deleting rows with same address


N

noel

I have a spreadsheet with 6 columns; 1-name (last name, first name),
2-address1, 3-address2 4-city, 5-state and 6-zip code. The spreadsheet shows
members of my organization. I am trying to stop sending two mailings to the
same address to save postage.

I need help in creating a formula or a macro to:

1. Sort by zip code, state, city, address1 and address2
2. Check to see if address is the same as one above.
3. If so, check to see if last name is the same as one above
4. If 3 & 4 are yes, take the first name of the second row and add it to
the first name of the first row, insert an ampersand (&) [e.g. Jane & Joe Doe)
5. Delete the second row

Any and all help will be appreciated!
 
Ad

Advertisements

D

Don Guillett

Fairly simple. Easier if you just send your wb to my address below instead
of me re-creating it.
 
Ad

Advertisements

D

Don Guillett

Sent this

Sub combinenames()
mc = 2 'col B

lr = Cells(Rows.Count, mc).End(xlUp).Row
For i = lr To 2 Step -1

If Cells(i - 1, mc) = Cells(i, mc) Then
p1 = InStr(Cells(i - 1, mc - 1), ",") - 1

If Left(Cells(i - 1, mc - 1), p1) = Left(Cells(i, mc - 1), p1) Then
Cells(i - 1, mc - 1).Value = Cells(i - 1, mc - 1) _
& " &" & Right(Cells(i, mc - 1), Len(Cells(i, mc - 1)) - p1 - 1)
Rows(i).Delete
End If

End If
Next i
Columns(1).Columns.AutoFit

'sort
Range("A1:G" & lr).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
Fairly simple. Easier if you just send your wb to my address below instead
of me re-creating it.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
noel said:
I have a spreadsheet with 6 columns; 1-name (last name, first name),
2-address1, 3-address2 4-city, 5-state and 6-zip code. The spreadsheet
shows
members of my organization. I am trying to stop sending two mailings to
the
same address to save postage.

I need help in creating a formula or a macro to:

1. Sort by zip code, state, city, address1 and address2
2. Check to see if address is the same as one above.
3. If so, check to see if last name is the same as one above
4. If 3 & 4 are yes, take the first name of the second row and add it to
the first name of the first row, insert an ampersand (&) [e.g. Jane & Joe
Doe)
5. Delete the second row

Any and all help will be appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top