Excel 2003

  • Thread starter Thread starter MRJ
  • Start date Start date
How do I eliminate duplicate addresses in a mailing list?
Click to expand...

Hi,

Depends entirely on what your list looks like. Is it a string all in
one cell, is it a row of cells with a name in each one, is it a column
of cells etc etc.
Best to give an example of your data before anyone can give you a
meaningful suggestion / solution. With your question as it stands, I'd
say select them and hit 'delete' :).

Cheers,
Ivan.
 
I'm not exactly sure how to post a sample, but the way the spreadsheet is
laid out is with info in columns: first name, last name, street address 1,
street address 2, city, state, zip.

If I am dealing with a short list, it is simple to select the duplicates and
delete. When you're dealing with a list of 5,000 though, another method is
required.

Thanks,

MRJ
 
I don't know how to post a sample, but I had mentioned to someone earlier
that I have the spreadsheet laid out in a standard format - firstname,
lastname, streetaddress1, streetaddress2, city, st, zip.

I could select duplicates and delete in a short list no problem, but there
must be a faster way to do it when you have thousand of names on your list.

Thanks again for your help!

MRJ
 
I don't know how to post a sample, but I had mentioned to someone earlier
that I have the spreadsheet laid out in a standard format - firstname,
lastname, streetaddress1, streetaddress2, city, st, zip.

I could select duplicates and delete in a short list no problem, but there
must be a faster way to do it when you have thousand of names on your list..

Thanks again for your help!

MRJ






- Show quoted text -
Click to expand...

Hi MRJ,

OK, I reckon the simplest way ten would be to click somewhere in the
list, then head for 'Data' -> 'Filter' -> 'Advanced Filter' (assuming
you are in Excel 2003 or prior... goodness knows where they have
hidden it in 2007 :)).

In the Advanced filter dialog box, change to 'Copy to another
location' then set the 'Copy to' RefEdit box to an empty spot on your
sheet. - Say your list starts in A1 and takes up Cols A:G, just set it
to K1 or something.

Then tick 'Unique records only and hit 'OK'

Cheers,
Ivan.
 
MRJ said:
I'm not exactly sure how to post a sample, but the way the spreadsheet is
laid out is with info in columns: first name, last name, street address 1,
street address 2, city, state, zip.

If I am dealing with a short list, it is simple to select the duplicates and
delete. When you're dealing with a list of 5,000 though, another method is
required.

Thanks,

MRJ
Click to expand...
Perhaps you could use the AND() function.

A B C D E F G
FNam LNam Addr1 Addr2 City St Zip
John Doe 13 Main St Anytown RI 2913
John Doe 13 Main St Anytown RI 2913


First you need to sort the data:

Data > Sort with the first Sort By being Addr1 and the second Sort By being
Addr2 and the third Sort by being City.

In cell H1 type in FALSE.
In cell H2 enter:
=AND(A3=A2,B3=B2,C3=C2,D3=D2,E3=E2,F3=F2,G3=G2)

Copy the equation down for the entire list of data.

If each and every cell on row "3" equals each and every corresponding cell
on row "2", then the answer is TRUE.
If not, the answer is FALSE

Delete every row whose "answer" is TRUE.
 
JohnL said:
Sorry, in rereading my post I caught an error.
Click to expand...
In cell H2 (NOT cell H1) type FALSE.
In cel H3 (NOT cell H2) type in the equation.

Sorry for the confusion.
 
Back
Top