SOLVED

Index Match issues.

Brass Contributor

I have sever databases that show all employees ID's, which i use as the key to matching and analyzing all of my data.  I have come across a database that is 3rd party, but I need a lot of the information provided from their database and they DO NOT show a employee ID, AND their names are NOT spelt the same as my 1st party data in Table 2.

 

Table 1 is the 3rd party database, and you will see that the names have differences than our 1st party data.  Small things like missing a period after middle initial, First names not complete, or full names not listed in the same manner.  This list is thousands of rows long, I just used this as an example.  Just mentioning this because there are a lot of similar errors in all rows.

 

What I need is the Employee IDs in my 3rd party Data (Column B in Table 1), so that I can put the data into my analytical tables to be analyzed in other programs.  I have tried index match, and have not had luck in using 1 or -1 as the match criteria.  It gives me the same badge numbers for similar names in both scenarios.  

 

I use VBA, and Functions well, so any type of feedback would be helpful in either platform.  Thank you all for your time.

 

ADGToreUp_0-1656439754382.jpeg

 

2 Replies
best response confirmed by ADGToreUp (Brass Contributor)
Solution

@ADGToreUp 

I'd start by inserting a new column in column B.

Enter the following formula in B2 and fill down:

=SUBSTITUTE(A2,".","")

Then use column B instead of column A in VLOOKUP or XLOOKUP formulas in column C to retrieve the ID from what are now columns E/F.

Check how many IDs are missing. If it's a small number, I'd look them up manually, but if there are many, we might look at methods to get at least part of those.

 

Thank you! This ended up inspiring me to break down the names in both databases through the power query, and i was then able to match everything based on Last name, First name, and Middle Initial, which worked very well. I appreciate your help!
1 best response

Accepted Solutions
best response confirmed by ADGToreUp (Brass Contributor)
Solution

@ADGToreUp 

I'd start by inserting a new column in column B.

Enter the following formula in B2 and fill down:

=SUBSTITUTE(A2,".","")

Then use column B instead of column A in VLOOKUP or XLOOKUP formulas in column C to retrieve the ID from what are now columns E/F.

Check how many IDs are missing. If it's a small number, I'd look them up manually, but if there are many, we might look at methods to get at least part of those.

 

View solution in original post