"Remove Duplicates"

[Charles]

OK, I'm a real novice here (not familiar with formulas, etc.) and I'm using
Excel 2007 for the first time (was using 2003) with my mailing list of
3,000+. I am constantly adding names/addresses and updating the list. Many
times I'll add, say, Mary Jones at 123 Elm St while not realizing that I
already have Tom Smith at the same address. So, in 2007, there is this great
buttton "Remove duplicates". To try and be sure I'm really removing a
duplicate I check off zipcode (9 digit) and street address. However in the
case of an apt bldg (if the apt # is not listed) that wouldn't work and
possibly there may be some other error. So, I'd like to be able to see what
is removed, (before it is actually deleted) but there doesn't seem to be any
option for that. And that (finally) is my question.
Thanks in advance for any assistance.

 

[Myrna Larson]

I'm not using XL2007, but I think this should still work. Let's say the
address is in column F.

In a "helper" column, put this formula =COUNTIF(F2,F$2:F$3500). Then you can
use AutoFilter on this column to display entries > 1.

 

[Charles]

Thank you for the response Myrna. It took a few minutes for me to figure out
( isaid I was a real novice) exactly what to do but I did. However there are
two problems that popped up. First, it showed me duplicates strictly based
on the numerical part of the address, i.e., all the "1's" then all the "10's"
and everything with a "10" in it such as "100", "1000", "1001", "1010" etc.
It did the same thing with the "2's" and "20's", etc.
Second was that allthough it showed me the addresses (unlike the "remove
duplicates button" in Excel 2007) it didn't show me where ion the list they
were. I can overcome that by doing a data sort by zip code and then they
would be easy to find. However if I sort by zip (9 digit) then the formula
you gave me would show me even more duplicates.
Just as an FYI, this mailing list is for a charity that I volunteer for and
therefore duplicates are wasted money on each mailing.

 

[Myrna Larson]

I had assumed you had the house number and street all in one cell. Is that not
the case? If so, maybe you can create a helper column over on the right, with
a formula like this. I will assume the street number is in column C, the
street name in D, the City in E, State in F, Zip in G

=G2&" "F2&" "&E2&" "&D2&" "&C2

That will give you the zip, state, city, street and house number all in a
single cell

Now you can sort on that column, and/or do a COUNTIF formula on that column to
identify duplicates.

In case the data has to be put back into its original order, and you cant get
that by sorting, e.g. by name, then you also need to create a column that has
a number corresponding to the original position in the list. You can do that
by selecting an empty column on the right, typing a 1 in the top cell, then
select the rest of the cells in the column and Edit/Fill/Series.