Lookup / Match

[sue0912]

Hi,

I have a large spreadsheet that has several "address" columns..see below

Business Address, Business Address2, Business City, Business State, Business, Zip, Current Address, Current Address2, Current City, Current State, Current Zip, Preferred Address, Preferred Address 2, Preferred City, Preferred State, Preferred Zip.

All this data comes from several different spreadsheets that I combined into 1 using Access. It is about 1900 records.

My purpose is to do a mass mailing, but some of these addresses in these columns may be the same.

My question is....Can and I how do I lookup and match if any of these columns have the same address?

I know how to do a lookup match using a column from a different worksheet but not within the same worksheet.

Any help is much appreciated.
Thanks
Sue

 

[SkipVought]

hi,

If it were me, I'd use MS Query to ...

Select DISTINCT *
From [Sheet1$]

This query will return only distinct rows from your Sheet1. Best if row 1 has unique headings.

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Do you mean that addresses can be duplicated over the 3 different types (Business, Current & Preferred)?


If so, the anser is not quite as simple as using MSQuery over the whole data set

What I would do is utilise 3 columns at the end of your set of data to generate a unique key
=A2&B2&C2&D2&E2 for business address key
=F2&G2&H2&I2&J2 for business address key
=K2&L2&M2&N2&O2 for business address key

I would put these in columns P, Q & R and copy down

You can then do a countif formula to test the number of occurences of each address key - more than 1 means there is a duplicate

e.g.
=countif($P$2:$P$1900,P2) ---- to test just in column P
=countif($P$2:$R$1900,P2) ---- to test in the whole dataset

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Another option: use advanced filter to extract a unique list of addresses to use for the mailshot. Data, Filter, AdvancedFilter.... (xl2003)

Your extract range needs to contain just the field headings that, in combination, need to be unique. Don't forget to tick UniqueRecordsOnly.



Gavin