Identify Complete and Partial Matches in two different datasets, as well as no match cases

**cordyceps** · 08-07-2015, 06:48 PM

I have two spreadsheets of client account information from 2 different databases that need to be reconciled. My level of VBA skills is basic, so I'm a bit lost at where to start. My biggest issue is that there are errors in all of the information, including unfortunately the unique identifier, which was the main thing we relied on to identify a client in database A with his account in database B. So I can’t just try to match by a unique identifier, or match row by row, since potentially a client can be anywhere in the spreadsheet if their unique identifier has been mis-entered.

I need something that will:

1. Identify rows where with exact matches in the relevant data (Client Name, Date of birth, email address, Identifier #) and list them or pull them to another sheet

2. Identify rows where the data has a partial match on some of the variables and list them and list them or pull them to another sheet

3. Identify rows in the first data set that don't have any matching variables in the second dataset at all.

I know enough to figure out how to do the first one of the three. Any help pointing me in the right direction would be appreciated.

**xladept** · 08-07-2015, 06:59 PM

Hi cordyceps,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**cordyceps** · 08-07-2015, 11:43 PM

Thank you for your advice

I've included a mockup workbook with dummy data subbed in for client data. I've reproduced the type of errors I'm facing. DATA SET 1 and DATA SET 2 are the worksheets representing the data exported from the system (before), NO MATCH, PARTIAL MATCH, and FULL MATCH are mockups of outputs (after) that I would like to generate. I've only included 150 cases in this, but the actual databases I'm dealing with are several thousand.

A Final note is that the second database also has a system identifier that isn't in the first database. I don't need to match that variable. That was an extra layer of difficulty when I was trying to figure out how to do this.

Just a little explanation, we work with two separate systems with no direct bridge currently. Reconciliation requires manually entering the Universal Identifier generated in the first system into the second to correlate the cases, but we've discovered that people were really not being careful or being given incorrect information by clients, making the task much harder.

Again, any help anybody can give is very much appreciated!

**xladept** · 08-08-2015, 07:17 PM

Hi cordyceps,

Try this:

Please Login or Register  to view this content.

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name

**cordyceps** · 08-08-2015, 10:58 PM

It works perfectly, and very fast! Thank You!

**xladept** · 08-09-2015, 12:10 AM

You're welcome!

**cordyceps** · 08-09-2015, 02:38 PM

Out of curiosity, if I wanted to add an extra column to be checked (say by moving all the calls the code makes one to the right, and adding an extra term to match), how would I go about it. There's another field in the database (client personal ID#, usually a driver's license) that I didn't bother to export when I started trying to figure out how to do this with excel formulas. It would make a 100% guaranteed match easier to determine.

And really, really thanks again

**xladept** · 08-09-2015, 03:47 PM

Hi CordyCeps,

Below in red are areas where you would need to add the new field or change the index:

Please Login or Register  to view this content.

**cordyceps** · 08-09-2015, 05:29 PM

Thank You once again!

**xladept** · 08-09-2015, 07:02 PM

You're welcome!

Identify Complete and Partial Matches in two different datasets, as well as no match cases

LinkBack

Thread Tools

Rate This Thread

Display

Identify Complete and Partial Matches in two different datasets, as well as no match cases

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Re: Identify Complete and Partial Matches in two different datasets, as well as no match c

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Multiple partial matches

Partial Match Query - VLookup not giving accurate results for some obvious matches.

incorporate the formatting from the “Partial” column to the “Complete”?

MATCH% on similarity from different Datasets

How to match partial cell text and sum relating values for multiple matches

[SOLVED] Autofiltering for partial matches

Calculating number of complete and partial months

Bookmarks

Bookmarks

Posting Permissions