deleting duplicate fields

[elaine]

I have a mailing list in excell worksheet. I want to mail only one letter to
household. however, ther are sometimes 3 different names at same address. How
can I filter out and delete duplicate addresses.

example.
jane smith 123 south street anytown, usa
john smith 123 south street anytown, usa
deb smith 123 south street anytown, usa
john smith 566 different street anytown, usa

 

[Gord Dibben]

Elaine

Suggest you go to Data>Filter>Advanced Filter.

"Copy to another location" and "Unique records only"

Filter on the number and street name to get

jane smith 123 south street anytown, usa (john and deb will be filtered out)
john smith 566 different street anytown, usa

For more on this see Debra Dalgleish's site.

http://www.contextures.on.ca/xladvfilter01.html#FilterUR

Gord Dibben Excel MVP

 

[Jim May]

For this to work for me, I had to:
Assuming Your data is in:
A B C
1 Name Address Town/Ctry
2 jane smith 123 south street anytown, usa
3 john smith 123 south street anytown, usa
4 deb smith 123 south street anytown, usa
5 john smith 566 different street anytown, usa

I wanted also to use Advanced Filter, so I had to:

!) Insert a new Column between above A & B naming it in Cell B1 (now)
LastName
in cell B2 enter =RIGHT(A2,LEN(A2)-FIND(" ",A2)) [[<<this formula
assumes only two names in column a]] then copy down as far as needed. Now
you got (In New Column B) only the LastName. << I usually do a Copy &
Paste_Special Values at this point to convert any formularized data into
values For what its worth...>>

2) Copy into Cells F1:G1 LastName Address << Exactly like in
B1/C1.

3) Highlight the range B11000 ((EXCLUDING COL A)) and click on Data,
Filter, Advanced. You decide whether to copy elsewhere (on same sheet) or
in place, but
in the Criteria box enter $F$1:$G$1

4) Be sure to Check the Unique Box;

5)OK, Done

Report Back if problems.............
HTH
Jim May