cross-sheet duplicate search

Matt Wiese

Thanks in advance for reading this

I have multiple inventory sheets, each has a column with a formula to flag duplicates within a specific column:

=IF(ISBLANK([Serial Number]1), "blank", IF(COUNTIF([Serial Number]:[Serial Number], [Serial Number]1) > 1, "yes", "no"))

Form there, I apply some simple conditional formatting and all is well. My question is this; has anyone figured out how to search for duplicates in entire columns between two different sheets?

L_123

=IF(ISBLANK([Serial Number]@row), "", IF(COUNTIF({New Sheet 2 Range 1}, [Serial Number]@row) > 0, "Yes", "No"))

 

You don't really have to change anything, just use a cross sheet column reference. You will have to select the blue "Reference another sheet" when doing the {other sheet reference}, and select the column header to get the entire column as your reference.

Matt Wiese

thanks for the response Luke! Here is the formula that I am using with no errors:

=IF(COUNTIF({Master IT Asset Donation List Range 1}, [Serial Number]1) > 1, "yes", "no")

but this is still failing to flag (provide "yes" response) any duplicates per testing performed. I've taken some Serial Numbers and pasted them into the referenced sheet for testing. Made sure to save and refresh etc

L_123

your equation will only check past 1 duplicated because you used a >1 instead of >0. If you duplicated the serial number twice on the Master IT Asset Donation List page I think it will work.

Matt Wiese

Thanks Luke!

=IF(COUNTIF({Master IT Asset Donation List Range 1}, [Serial Number]1) > 0, "yes", "no")

This works You are a huge help!