cross-sheet duplicate search

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?

Find more posts tagged with

Sheets

IT & Operations

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

SUmmary in Report Widget
How can i create a Report widget that only shows a status with "red status" from my main data source? Do i need to create a separate data source on just my "red status"
Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…