excell name comparison macro....

hi guys,...
I desperetely need a big macro(script) smt. like this;
have two different *.xls files, macro will ask which; and open the first & second
files, it will also ask which columns are the "name" columns or detect them...

in first file , lets say a-b-c-d-e columns are full and some are numbers, and
column b has ; "name-surname" records of 2000 persons...

second file, nearly the same, but 1000 records....

now, I need the macro to open an 3rd. sheet which contains the differences;
the names in 1000 records (2nd file), but not in 2000 records (1st file)...

names are composed of text including space , like "John Smith", they may
be also misstyped; as "Jonh Smiht" ....

so , this macro should "feel" that this two are the same

also , articles; like "the, on, of, from,etc..." should not be taken into
consideration....

so long this far,...but a big macro.....
tnx for your helps.

Hi!

I wouldn't go near a macro on this one.

First the "intuitive" bit of fuzzy matching. With only 1 or 2000 records, I suggest you first sort them on the name column and then apply Data > Subtotals. You will soon see which need correction. After all, there could be people with names "mis-spelt" from birth: if you can program to recognise them, you can do it quicker by eye and by hand.

Now to identify items in the short list which are not in the long one.

I'll assume the long list is named LongTable. Alongside the first item in the short list put =countif(LongTable, B1) where B1 is the first cell containing a name in the short table. Copy this down all 1000 records.

If 1 appears as the result of this formula, there is exactly 1 item in the long list which matches the item in the short list. 0 means the item is missing. 2 means you have duplicates!!

This should enable you to identify which items you need to copy across from sheet2 to sheet1. To pin them down, use Data > Filter > Autofilter and select the column with the 1's and 0's in it. Filter for the 1's. This is your required group. It can be copied by selecting the visible rows: F5 > Special >Visible cells only. The paste them where you want them.

Alf