Hi Team, I have two excel. I would like to compare them and see if there is any mismatch. * Compare the two files and find a - same vendor number, same vendor name but different email id. b - same vendor number, same email id but different vendor name The final data will be exported to new excel file, Please let me know how to go about this and attached the excel file, Invoice_ext_file.xlsx Master.xlsx

Fuzzy between two excel files for anamoly detection

sriniprad08

Hi Team,

I have two excel. I would like to compare them and see if there is any mismatch.

Compare the two files and find
a - same vendor number, same vendor name but different email id.
b - same vendor number, same email id but different vendor name

The final data will be exported to new excel file,

Please let me know how to go about this and attached the excel file,

Invoice_ext_file.xlsx

Master.xlsx

Fuzzy Match

Accepted answers

jvansistine

In that case, you could use the Fuzzy Match tool, which will give you the match score. There are a few options for setting that tool up, so definitely check out the help page. But that tool will allow you to find an acceptable threshold for "close enough" matches.

Compare files.yxmd

jvansistine

I think I would do something like this.

Do the fuzzy matches in parallel, and then use find/replace to substitute the standardized value from the master back on your invoices list.

I'm not sure how you can do them in a single step, since fuzzy match only looks down one column for its matches.

Compare files.yxmd

All comments

jvansistine

Hi @sriniprad08 ,

Here is one way you could do it. Since the Number always has to match, you could just use a join tool on that field, and then calculate the other matches.

This will duplicate some rows if the number exists multiple times in the master, but that will allow you to flag the correct one to keep or drop.

-Jim

Compare files.yxmd

jvansistine

Depending on the size of your actual files, it might also be a good candidate for a batch macro. You could loop through the values in the Invoice list one at a time and compare them to the master individually that way.

sriniprad08

hi @jvansistine

Thank you so much very helpful. But is it possible to get the match score. The one's i marked in Yellow and Red are almost same. So we need to identify those as well. For eg. "Noal lime limited" and "Noal lime limited companies " are almost same except one extra word "companies" . Can we capture these?

Also you mentioned about batch macro. Can you please share some link or thoughts how to go about this as well?

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167