I have a spreadsheet with adresses in some of them are the same. Is there a
function that will find addresses that only appear once in the list. Any
Help would be appreciated.
--
Regards Clair

You can not account for the same adress typed in differently such as PO Box
vs. P.O. Box or South Maple vs. S. Maple. There are just to many options.
That said
If you have column labels in row 1, Street address in Column B and Postel
code in Column E, and less than 5000 entries, in the next available column
in row 2
=SUMPRODUCT(--(TRIM(B$2:B2)=TRIM(B2)),--(LEFT(E$2:E2,5)=LEFT(E2,5)))
Copy down
Any that return 1, you keep, 2 or above means that is the 2nd etc occurance
of that address on your list.
I am trying to account for 5 digit postal code vs. zip+4, however, if there
is mix of zip as number, zip as number stored as text, new England zips
missing the lead zero (less than 5 digits, these will foul that up. Fix this
with the following formula in row 2.
=IF(LEN(E2)<5,0,"")&IF(LEN(E2)<4,0,"")&IF(LEN(E2)< 3,0,"")&IF(LEN(E2)<2,0,"")&E2
Copy down, then either paste special values over your original zip code or
point the references to column E in the first fomula to your new zip code
column.


"Chester1" wrote in message
...
I have a spreadsheet with adresses in some of them are the same. Is there
a
function that will find addresses that only appear once in the list. Any
Help would be appreciated.
--
Regards Clair

What I am trying to do is find the addresses that only appear once in the
column and then I need to delete those particular addresses. Hope this makes
a little more sense??
--
Regards Clair


"Bob Tarburton" wrote:

You can not account for the same adress typed in differently such as PO Box
vs. P.O. Box or South Maple vs. S. Maple. There are just to many options.
That said
If you have column labels in row 1, Street address in Column B and Postel
code in Column E, and less than 5000 entries, in the next available column
in row 2
=SUMPRODUCT(--(TRIM(B$2:B2)=TRIM(B2)),--(LEFT(E$2:E2,5)=LEFT(E2,5)))
Copy down
Any that return 1, you keep, 2 or above means that is the 2nd etc occurance
of that address on your list.
I am trying to account for 5 digit postal code vs. zip+4, however, if there
is mix of zip as number, zip as number stored as text, new England zips
missing the lead zero (less than 5 digits, these will foul that up. Fix this
with the following formula in row 2.
=IF(LEN(E2)<5,0,"")&IF(LEN(E2)<4,0,"")&IF(LEN(E2)< 3,0,"")&IF(LEN(E2)<2,0,"")&E2
Copy down, then either paste special values over your original zip code or
point the references to column E in the first fomula to your new zip code
column.


"Chester1" wrote in message
...
I have a spreadsheet with adresses in some of them are the same. Is there
a
function that will find addresses that only appear once in the list. Any
Help would be appreciated.
--
Regards Clair

Then you delete those that show "1" with this method.
Probably need more specifics about how your database is set up (rows used,
columns used, headers) to give a more specific answer.

"Chester1" wrote in message
...
What I am trying to do is find the addresses that only appear once in the
column and then I need to delete those particular addresses. Hope this
makes
a little more sense??
--
Regards Clair


"Bob Tarburton" wrote:

You can not account for the same adress typed in differently such as PO
Box
vs. P.O. Box or South Maple vs. S. Maple. There are just to many options.
That said
If you have column labels in row 1, Street address in Column B and Postel
code in Column E, and less than 5000 entries, in the next available
column
in row 2
=SUMPRODUCT(--(TRIM(B$2:B2)=TRIM(B2)),--(LEFT(E$2:E2,5)=LEFT(E2,5)))
Copy down
Any that return 1, you keep, 2 or above means that is the 2nd etc
occurance
of that address on your list.
I am trying to account for 5 digit postal code vs. zip+4, however, if
there
is mix of zip as number, zip as number stored as text, new England zips
missing the lead zero (less than 5 digits, these will foul that up. Fix
this
with the following formula in row 2.
=IF(LEN(E2)<5,0,"")&IF(LEN(E2)<4,0,"")&IF(LEN(E2)< 3,0,"")&IF(LEN(E2)<2,0,"")&E2
Copy down, then either paste special values over your original zip code
or
point the references to column E in the first fomula to your new zip code
column.


"Chester1" wrote in message
...
I have a spreadsheet with adresses in some of them are the same. Is
there
a
function that will find addresses that only appear once in the list.
Any
Help would be appreciated.
--
Regards Clair