Checking for Duplicates and then Checking for the highest ID Code.

jdhubbard09

Hi there!

We use Smart Sheet for tracking multiple components of our department. On one sheet, I want a formula to check for duplicate names and then look at the ID Code Column and I want a box to be checked for any ID Code that isn't the highest of the duplicates.

I have gotten the first part of the formula to work. I have a column where the box is checked if there is a duplicate name. I use the formula below:

=IF(COUNTIF([Contractor Name]:[Contractor Name], [Contractor Name]@row) > 1, 1, 0)

I can't get the second part of the formula to work in where it then looks at the ID Code and does not check the box for the highest ID Code.

Any suggestions would be appreciated!

Paul Newcome

You will need a helper column that pulls in the highest id code for the name on each row.

=MAX(COLLECT([ID Code]:[ID Code], [Contractor Name]:[Contractor Name], @cell = [Contractor Name]@row))

Then the flag formula would look something like this:

=IF([ID Code]@row< [Helper Column]@row, 1)

jdhubbard09

=IF(COUNTIF([Contractor Name]7:[Contractor Name]110,[Contractor Name]@row)>1, NOT(MAX([ID Code]7:[ID Code]110,[ID Code]@row))1,0)

I also tried this formula and was returned as formula syntax isn't quite right.

Paul Newcome

You will need a helper column that pulls in the highest id code for the name on each row.

=MAX(COLLECT([ID Code]:[ID Code], [Contractor Name]:[Contractor Name], @cell = [Contractor Name]@row))

Then the flag formula would look something like this:

=IF([ID Code]@row< [Helper Column]@row, 1)

jdhubbard09

This did exactly what I needed it to!! Thank you so much for your help!!!!

Paul Newcome

Happy to help. 👍️