Duplicates - Check mark all occurrences except the last/newest one

edited 12/09/19 in Smartsheet Basics


When duplicates exist, is there a way to check mark all occurrences except the last occurrence? 

I used the following formula, but it check marks all occurrences except the first occurrence. 

=IF(ISBLANK([Badge ID Number]1), "", IF(COUNTIF([Badge ID Number]$1:[Badge ID Number]1, [Badge ID Number]1) > 1, 1)) 

My sheet automatically adds new lines of data from a different source on a daily basis. The purpose is so that if a new entry has the same badge ID number as a previous entry, the old entries will turn gray and only the newest entry for that badge ID will be white. 

Thanks in advance for any advice! 

(I attached a screenshot with made up badge ID numbers)

What is happening.PNG