Comparing Data without a unique identifier

[Realty Goddess]

I appreciate your assistance!

I am tasked with comparing 2 worksheets. One is downloaded from our Access database into an Excel spreadsheet. The other is from a State database. Both have similar information, but there is no unique identifier.

I am hoping that there is a way to compare a column that contains the Vendor name.

In spreadsheet 1, the Vendor may be listed as Huff, George
In Spreadsheet 2, the Vendor may be listed as Huff Estate.

I am working with thousands of records, so this is not an easy task to do manually. These spreadsheets do have some similar columns, such as County (there could be hundreds of records with the same county), but the only data close to being unique is the Vendor column.

I tried using the Excel Analyzer Add-in, but that didn't help. I am not an IT or programmer - my job is working with Realty Grants.

In Access, I use the following criteria to enable users to query a particular unit name without having to know the exact name: Like "*" & [Enter Part of Unit Name] & "*" Is there anything similar I can create in Excel to look for just a piece of the Vendor's name to match the other spreadsheet?

We can purchase software, if you have any recommendations.

Any suggestions?

 

[Phox]

It may be worth it to manually maintain a separate table that compares the vendor names of each. You are asking to be able to query with part of a name... I'm having trouble understanding why the built-in Find tool wouldn't work for that.

 

[Realty Goddess]

If we used the find tool, we would be manually comparing 1 record at a time. One of our state spreadsheets has 11,000 records. I am hoping there is a program out there, or that Excel can be forced to compare records using VBA, so that the program would do the comparison for us.

 

[VoG]

Take a look at Alan's Fuzzy Lookup http://www.mrexcel.com/forum/showthread.php?p=955137#974873

 

[Realty Goddess]

Thanks! This is definitely not something to read and understand on a Friday......LOL

 

[al_b_cnu]

Hi Realty Goddess,

FuzzyVLookup can be a bit daunting at first sight, but reading your post, it looks like you just need something like '=Vlookup("*" & lookup_string & "*", ...)

but post some sample data, and we can go from there.

 

[Realty Goddess]

You are truly a lifesaver! My boss is in a dreamworld, thinking I understand this stuff, LOL

Here is an example:

My records

State Grant Vendor
MN FW-3-L-1 Hoff Estate
MN FW-3-L-2 Garrity, P

State records

MN FW-3-L 1 Jed Hoff
MN FW-3-L Garrity & Peters

I am assuming that perhaps there is someway to use the vendor name to lookup. There may be 10-30 different records in one grant, so that is not a good lookup.

I tried the Lookup feature on tools, and it will find matches, one record at a time. However, I have thousands to work on, so that isn't alot of help.

Your assitance is most appreciated!!

 

[al_b_cnu]

Hi,

First cut :

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	My Records	*	*	*	*	*	State	*	*
2	*	*	*	*	*	*	*	*	*
3	State	Grant	Vendor	Lookup Row	*	*	State	Grant	Vendor
4	MN	FW-3-L-1	Hoff Estate	6	*	*	MN	FW-3-L-2	Jed Hoff
5	MN	FW-3-L-2	Garrity, P	7	*	*	MN	FW-3-L-1	Garrity & Peters
6	*	*	*	*	*	*	MN	FW-3-L-1	Jed Hoff
7	*	*	*	*	*	*	MN	FW-3-L-2	Garrity & Peters
Sheet1

I have used the Grant column as a group identifier, so the code will return the best matching row for that group ONLY.

Inote that I have used algorithm 2, which matches on pairs, then triplets then quads etc as this seems the most appropriate on this occasion.

As this function is VERY CPU hungry, I've returned a row number rather than a value. This can then be used as part of an INDEX or OFFSET formula to return the field(s) required.

 

[Realty Goddess]

Thanks for the response, Alan. Since I still don't "get it," I sent a request to MrExcel for an estimate to set up an application for me. I just don't have the time to devote to trying to work this out.