Complicated Cross Spreadsheet Search

I have 2 spreadsheets.

One of them (spreadsheet A) has tens of thousands of rows with all sorts of data.

The second (spreadsheet B) is smaller, just a few thousand rows.

The data columns do not match exactly. The first spreadsheet, spreadsheet A many of the data are duplicated partially or fully, for example, a name in spreadsheet A is listed 200 times in spreadsheet B, but some of the other values are different, like address. However the address may not be an exact match bewtween spreadsheets.

I need some way to do a wildcard search to return a value (like vlookup) from a partial match between spreadsheets. But it has to be able to return multiple values/rows to each individual row in spreadsheet B. Doing this by hand would take weeks and weeks if not months, but with some sort of sieve between the two with partial matches, it would narrow down what I have to sift through and save me an incredible amount of time.

Is there anyway to do this with some special formula, or write some sort of script in visual basic?

Thanks!

A quick example of what I mean by tedious work:

There are about 2600 entries of the same name (each of these is a different account or client, but with the same first/last name). Each entry has up to 6 addresses. I need to match the correct one from Spreadsheet B to the one of these 12,000 addresses correctly, or at least find a way to not have to look through 12,000 addresses just to match up one account/client. Unfortunantly there are no distinguishing or individual numbers (like an account number) to match it up to, which is the whole reason I have having to do this....

I have developed 2 routines that may assist.

The first one will compare one line with any number of (sort of) duplicated lines . Any cells that match will be deleted. You are left with 1 line and bits from other lines that do not match (spelling errors). A human can decide which to keep.

The other routine places one set of data alongside another set. A column in each set is deemed to be the matching reference (could be several columns concatenated). The routine inserts a part row in either data set leaving the matching rows on one row and non matching rows on their own.

Let me know if this is of interest.

Regards
Alastair

The second one might be kinda useful. What I need is to match up to see which one is a true match (if any) from the larger spreadsheet w/ "duplicates" (they are not duplicates exactly, just accounts with the same name on them) and then compare other data to see which one I am needing, and then take one cell of data and move it to the other spreadsheet if it is the correct account/name verified by a human.

I have not forgotten you - just pressures. The routine is on another computer and I will get it tomorrow.

Regards
Alastair

ok thanks!

Here's the file. Read the instructions first. Have fun.

Let me know how you get on.

Just to explain: the first routine that you dismissed out of hand will give

Ref Col A Col B Col C

Ref1 A1 B1 C1
Ref1 A2 C1
Ref1 A1 B2

will give

Ref1 B1
Ref1 A2
Ref1 A1 B2 C1

enabling humans to decide between A1 and A2

Regards
Alastair