Flagging duplicates from 2 separate columns

Greetings -

I'm having an issue trying to flag duplicates from 2 separate columns in the same sheet. I have been able to flag duplicates in a single stand-alone column but trying to using the same formula with the "COUNTIFS" function is leaving the flagged cell blank (as seen in row 2 of the screenshot attached).

Here is the formula I am currently using: =IF(COUNTIFS([Tracking ID]:[Tracking ID], [Tracking ID]3, [Tracking ID SCAN]:[Tracking ID SCAN], [Tracking ID SCAN]3) = 0, 1, 0))

Any thoughts or suggestions?

Thanks in advance!

Smartsheet Screenshot dup flag.PNG

Find more posts tagged with

Formulas

Services

Support

IT & Operations

Conditional Formatting

Cell linking

Comments

Paul Newcome

I'm not sure exactly what you are trying to accomplish. Using your screenshot...

Are you trying to flag the row because it has identical values in the Tracking ID and Tracking ID SCAN columns?

What exactly is your criteria for a duplicate flag?

Jack C

Hello Paul,

I am trying to flag duplicates not only for the same row but any data in the each of the 2 columns (Tracking ID & Tracking ID Scan).

The "Tracking ID" column is a data dump from our internal platform.
The "Tracking ID Scan" column is a part # scanned into the Smartsheet using the Smartsheet Mobile App scanner on the first available cell in that column.

I need to identify if anything scanned into the "Tracking ID Scan" column is not anywhere in the "Tracking ID" column by the "Duplicate Flag" column not being checked.

I hope that helps. Please let me know if you have any other questions. Thanks!

Paul Newcome

So you just want to flag on a row by row basis if the Tracking ID SCAN is anywhere in the Tracking ID column?

Jack C

That is correct, any suggestions?

Paul Newcome

Put this in the checkbox column...

=CONTAINS([Tracking ID SCAN]@row, [Tracking ID]:[Tracking ID])

Jack C

Works perfectly! Many thanks Paul!

Paul Newcome

Excellent! Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.