Managing duplicates with the advanced filter

[gwh]

Hi everyone,

I need some help removing duplicates from a very large customer
database in a spreadsheet. I need excel to delete the row if there are
rows where the customer firstname and surname and also the address1
columns are identical. I tried the following without success:

I went into the Advanced Filter dialogue, I chose the "copy to another
location" radio button and also ticked "unique records only". I entered
Y1 as the cell where the duplicates would be copied to and then I
entered a criteria range. I just clicked on the first cell at the top
of the columns that I wanted to be my criteria columns, ie. firstname,
surname and address1. When I clicked OK it did copy a whole heap of
data that were obviously dupliates but it didn't delete the duplicates
from the original list, it only copied them to the specified cell
location.

I'm not even sure if you can do what I want to do but if anyone has any
tips I'd be grateful. Not sure if I'm doing the criteria procedure
properly.

Thanks for any help offered.

 

[jimdilger]

Hi,

I just had a go at this. I can get it to work when I use "advanced
filter" to filter the list "in place". For the range, choose just the 3
columns you want to examine for duplication. No criteria are needed.
Check unique records only.

This will hide the rows containing duplications, but not delete them.
You can then copy the visible rows (and all of your columns) to another
location (another worksheet so as to avoid the hidden rows).

Hope it works for you!

Jim

 

[gwh]

Thanks for that. I've just tried it and it works - I was able to copy
the rows that weren't hidden to another worksheet but I'm not sure how
to access the hidden rows, ie. the duplicates. The only thing I can do
is turn on the "show all" in the filter menu but this is no good
because it just recombines all entries again and I'd like to have the
duplicates isolated.

Is there any way to do this?

 

[jimdilger]

I don't see how to do it with the Advanced Filter. But here is
something you might want to try.

If the firstname is in col A, the surname in col B and the address1 in
col C, and the entries begin on row 2, then enter this formula into the
cell on row 2 of an empty column

=IF(A3=A2,IF(B3=B2,IF(C3=C2,"duplicate of above",""),""),"")

copy the formula down the column.

If all 3 conditions are true (row n has the same entries as row n-1),
then the cell will show "duplicate of above", otherwise it will show a
blank. Then, you can use AutoFilter to show just those rows with
"dupilcate of above" in the new row. A bit convoluted, but it seems to
work.
Good luck!

 

[gwh]

Thanks, I'll give it a go.

I don't see how to do it with the Advanced Filter. But here is
something you might want to try.

If the firstname is in col A, the surname in col B and the address1 in
col C, and the entries begin on row 2, then enter this formula into the
cell on row 2 of an empty column

=IF(A3=A2,IF(B3=B2,IF(C3=C2,"duplicate of above",""),""),"")

copy the formula down the column.

If all 3 conditions are true (row n has the same entries as row n-1),
then the cell will show "duplicate of above", otherwise it will show a
blank. Then, you can use AutoFilter to show just those rows with
"dupilcate of above" in the new row. A bit convoluted, but it seems to
work.
Good luck!