Checking multiple sheets for duplicates

Nathan Fraser

Hi,

I have a workspace that is being used for uploading new information by a group of techs. This information is checked and then uploaded a database by another team.

Currently, I have the sheet working to check duplicates within one sheet, but once the data is uploaded to the database the line is moved to another "completed" sheet.

I am looking to check two sheets for duplicates, the new model entry sheet, and then the completed sheet. So in the event that someone uploads the same information that has already been added to the database, then it will be highlighted and noted to the team doing the review.

This is that formula I am using for the duplicates on one sheet:

=IF(COUNTIF([Model #]:[Model #], [Model #]@row ) > 1, 1, 0)

Can I use a method of "Countifs" to check other sheets?

This is what I've tried: (I understand that it is missing the ">1" but I can't even get the formula to count. "excel replacement sheet 2" is referencing the entire Model # column on the second sheet, and the "Excel replacement sheet 3" is representing the Model #@row)

=COUNTIFS([Model #]:[Model #], [Model #]@row , {Excel Replacement Sheet Range 2}, {Excel Replacement Sheet Range 3})

Thanks,

Nathan

Paul Newcome

You would use something more along the lines of

=IF(OR(COUNTIFS([Model #]:[Model #], @cell = [Model #]@row) > 1, COUNTIFS({Second Sheet Model # Column}, @cell = [Model #]@row) > 0), 1)

Paul Newcome

No. You will still need to set up the [Cross Sheet Reference} using the "reference another sheet link" and whatnot. I generally rename my ranges so that I can easily identify what I am referencing when working through formulas. When writing out formulas in the Community for other users, I also do this so that you can see exactly what I intend the {Cross Sheet Reference} to be.

Paul Newcome

You would use something more along the lines of

=IF(OR(COUNTIFS([Model #]:[Model #], @cell = [Model #]@row) > 1, COUNTIFS({Second Sheet Model # Column}, @cell = [Model #]@row) > 0), 1)

Nathan Fraser

Ok,

So instead of clicking "Reference another sheet" while writing the formula, I can use the braces, name the second sheet and column directly in the equation?

Paul Newcome

No. You will still need to set up the [Cross Sheet Reference} using the "reference another sheet link" and whatnot. I generally rename my ranges so that I can easily identify what I am referencing when working through formulas. When writing out formulas in the Community for other users, I also do this so that you can see exactly what I intend the {Cross Sheet Reference} to be.

Nathan Fraser

I am still getting an "#Unparseable" error.

I tried to match only from sheet 1 to sheet 2 and not combine the two with "Or" just to troubleshoot:

=IF(COUNTIF({Second Sheet Model # Column}, @cell = [Model #]@row )>1,1,0))

One thing I'm wondering is when I make the reference to the "Second Sheet Model # Column" I am clicking on the Column title and it highlights the entire column, but when I was checking for duplicate values just on one sheet, I was typing [Model #]:[Model #] to set the range. If click tried to click the whole column to add it to the formula it would keep exiting the formula.

Paul Newcome

Try removing a closing parenthesis from the end.

Nathan Fraser

You nailed it!

Thank you. I'm still used to Excel automatically correcting those inaccuracies for me.

Greatly appreciated, I will try and implement the entire formula now.

Paul Newcome

Happy to help. 👍️