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

tzyj

New Member
Joined
Mar 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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

New Member
Joined
Mar 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 NumberNameAccount
12345XXXX123456789101
12345XXXX101987654321

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

SHEET2:

12345XXXX10198765


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

Board Regular
Joined
Aug 15, 2012
Messages
156
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

New Member
Joined
Mar 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,573
Messages
5,548,853
Members
410,880
Latest member
LBrand
Top