Partial match data in a cell when comparing two sheets for duplicates?

[tzyj]

So I have one workbook with two sheets

Sheet1 is my main sheet I’m working off of with a bunch of data including File Numbers and Account Numbers, Sheet2 is my reference sheet with a report of all the data of files in a certain status that I want to compare to sheet 1

What I want to do is compare both sheets to find duplicates in a certain account status

I have been using an IF(ISERROR(MATCH formula to match up file numbers so Sheet1 will tell me all the duplicates by file number that are on Sheet2. Example of formula:

=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),””,”Duplicate”)

But the problem is, some files have more than one account, for example, one account is in the status I want to be listed as duplicate, the other is not on the reference sheet but has the same file number

This causes the formula to list both accounts as Duplicate since I told the formula to check for a file number.

I could just switch the formula to match the account number, but to complicate it further, the accounts on Sheet1 and Sheet2 sometime do not match completely.

For example, Sheet1 may the full account number, while sheet2 may only have a partial, say 4-12 digits

Is there a way I can use the formula to match for the account number instead, but instead of an exact match, it will match the 16 digit account number on Sheet1, with the potential partials on Sheet2?

or is there a better formula or way to go about th

I know the 0 in my formula tells Excel to look for an exact match, but the other variables only tell it to look for data greater than or less than, but I don’t think that will work in my search.

Thank you!

 

[rondeondo]

please post a sample of your data in a file and/or provide some examples of values you want to consider matched. you can use the Mid() function to identify part of a string, so you'd need to add a column in your look up data.

 

[tzyj]

please post a sample of your data in a file and/or provide some examples of values you want to consider matched. you can use the Mid() function to identify part of a string, so you'd need to add a column in your look up data.

i can’t post the file but I can definitely provide hypothetical examples!

Sheet1 is a whole list of data I receive from a company

sheet2 is my company’s internal data

sheet1 data has some accounts that have already been taken care of on my companies end, and that’s the data in sheet2

I want to match the data in Sheet1 that matches sheet 2 HOWEVER, Sheet1 has multiple accounts for the same file, maybe 1 we’ve taken care of, one we haven’t. That’s why I can’t use the file number, but the unique account number

BUT, the company that sends us the data has access to the full account numbers (Sheet1and we may have the full account number, or 4-12 digits of it (Sheet2)

I need to be able to match partial account numbers on Sheet2 with the full account numbers on Sheet1

example of the data I need to match:

SHEET1:

File Number	Name	Account
12345	XXXX	123456789101
12345	XXXX	101987654321

Notice, the same File number but two different account numbers. This is the same file

SHEET2:

12345	XXXX	10198765

I need to match the account ending in 4321 on Sheet1 with the account 10198765 on Sheet2 (the partial

Hopefully this makes it more clear, I know it’s a weird situation haha

 

[rondeondo]

So is the partial account number in sheet 2 of variable length? I don't think I have enough to build a solution at this point. if the number in sheet 2 is always 4 less characters I can deal with that, if it could be anything between the full account number and 2 characters that can be dealt with but it'll be messier. Need a little more specifics as to what you need to match with what.

 

[tzyj]

So is the partial account number in sheet 2 of variable length? I don't think I have enough to build a solution at this point. if the number in sheet 2 is always 4 less characters I can deal with that, if it could be anything between the full account number and 2 characters that can be dealt with but it'll be messier. Need a little more specifics as to what you need to match with what.

Apologies!! But thank you so much for your time and help!

yes - the account number data on Sheet 2 is of variable length

so, the account number on Sheet 2 can range from 4-16 digits. Meaning, some accounts will have between 4-16 digits of any given account number

For example, Sheet 2 will have account numbers as such:

123456789102 <- 12 digits
8976 <- 4 digits
132476458976 <- 12 digits
7623 <- 4 digits
76392837 <- 8 digits (this is more rare though)

Generally, there will be 4-12 digits of the account number. Sometimes we get lucky and have all 16, but that’s not all the time