Excel Formula for Matching Multiple Text Strings Over Multiple Columns

[tvargas]

I am trying to figure out a good Excel 2007 match formula to get a result by finding a best
match of text strings separated by a space, and this data can be found in a few columns in a different worksheet.

Example:

I have a street address in 1 worksheet, but the exact format may not match my address database.
The 2nd worksheet is my customer master and the street address can be found in 1 of 3 columns.

I want to return the customer number of the best match to the address in worksheet 1.

I'm thinking this will be a version of index/match, but can't quite figure it out. Any help is greatly appreciated.

Thanks

 

[SkipVought]

Hi,

I've encountered tasks like this amfewmtimesmovermthe years. Variations on a theme is much better appreciated in works like The Carnival of Venice than in databases.

It takes knowing what your data SHOULD be (I assume you Standard is your address db) and identifying the various types of variations from the standard. MATCH will only help in a sorted table using the greater than/less than, +1/-1 MATCH TYPE. It will take many passes to categorize the various anomalies and perform close matches. LOTS OF MANUAL ANALYSIS!

There's no silver bullet.

As far as the one of three columns, I'd get the address into ONE COLUMN/multiple rows, using a UNION query before any analysis is performed.

Good luck! You'll need it.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[tvargas]

Hi Skip,

I appreciate your feedback. Thankfully the review wasn't too large, so just did it manually.

Any chance there is a VBA solution? Probably would include several loops. I'm just learning VBA, so this could be a helpful learning opportunity.

Thanks again!

 

[SkipVought]

Almost anything is possible. Do expect additional data to be compared and I'd guess also added to your address database?

If so, it appears that the process was fairly simple. So write out the steps and logic that you applied in each step and that specification can be the starting point for possibly coding a solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

And if/when you get stuck, ask VBA questions at forum707
Skip can help you there. too.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.