Check a box in new column if duplicate value is found within a particular column

Mary Corvalan

Hello,

I'm trying to move a row to a new sheet when a duplicate serial number is added to a sheet through a form entry. Is there a formula I can use to check a box if a duplicate value is found in a serial number column of a sheet?

Thanks,

Mary

Paul H

Your are looking for a if(max(collect to determine the newest entry, something like this

=(IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Serial Number]:[Serial Number], [Serial Number]@row)), 1, 0))

Kelly Moore

Hey @Mary Corvalan

Paul's formula above works terrific for newest entry. For specifically finding duplicates, you can use this approach to mark duplicate rows as they occur. You will need either the Created date or If you are adding rows chronologically system autonumber column [Row ID]. I will use the [Row ID] in my example but Created can be used instead.

This formula checks the original row when a duplicate is added.

=IF(COUNTIFS([serial number]:[serial number], [serial number]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) > 1, 1)

If you need the duplicate row marked instead, then use the formula below

=IF(COUNTIFS([serial number]:[serial number], [serial number]@row, [Row ID]:[Row ID], @cell > [Row ID]@row) =2, 1)

Will either of these work for you?

Kelly

Paul H

Your are looking for a if(max(collect to determine the newest entry, something like this

=(IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Serial Number]:[Serial Number], [Serial Number]@row)), 1, 0))

Kelly Moore

Hey @Mary Corvalan

Paul's formula above works terrific for newest entry. For specifically finding duplicates, you can use this approach to mark duplicate rows as they occur. You will need either the Created date or If you are adding rows chronologically system autonumber column [Row ID]. I will use the [Row ID] in my example but Created can be used instead.

This formula checks the original row when a duplicate is added.

=IF(COUNTIFS([serial number]:[serial number], [serial number]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) > 1, 1)

If you need the duplicate row marked instead, then use the formula below

=IF(COUNTIFS([serial number]:[serial number], [serial number]@row, [Row ID]:[Row ID], @cell > [Row ID]@row) =2, 1)

Will either of these work for you?

Kelly

Mary Corvalan

Thank you both so much!! I've been using the formulas and setting up my workflow. I ended up using The formula checks the original row when a duplicate is added but I changed the "<"to ">". For some reason that worked better. This selected the original entry and I set up a move automation to have it sent to an archive sheet. 😀