Need to highlight duplicates in two columns?

david.saunders

I need to highlight any duplicates in two separate columns.

Such as below screen shot: Need all # highlighted that are in Unit # column and the Asset on Rent Data. Its easy in excel, but can't figure it out on Smart Sheets.

Larry

To mimic the Highlight Duplicates Functionality in excel I use a Checkbox Column Formula & a Cond. Formatting Rule (2nd Image). In this case I just used 2 checkboxes & 2 format rules

Unit # Dupe formula:

=IF(COUNTIF([Asset on Rent Data]:[Asset on Rent Data], [Unit #]@row) > 0, 1, 0)

This looks at the entire Asset on Rent Data column and checks the box if the value in Unit # shows up in that column.

Asset Dupe formula:

=IF(COUNTIF([Unit #]:[Unit #], [Asset on Rent Data]@row) > 0, 1, 0)

Just inverse the range & cell we are checking against here. Then 2 rules, if Unit# Dupe is checked, make Unit # column red & vice versa for Asset Dupe.