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

kerwin

Hello,

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)

Paul Newcome

Do you have a way to determine the most recent entry such as a created column or something to that affect?

kerwin

Yes, I have a column that shows the date of each entry. 

Paul Newcome

Ok. So instead of trying a COUNTIFS, we will use a MAX to look at the most recent dates and a COLLECT together the dates for the corresponding badge number.

 

=IF(ISBLANK([Badge ID]@row), IF([Date Column]@row = MAX(COLLECT([Date Column]:[Date Column], [Badge ID]:[Badge ID], [Badge ID]@row, 1), "")

 

What this does is the COLLECT pulls all dates for the badge number, the MAX pulls the most recent date from all of those. If the date on that row equals the date pulled from the MAX/COLLECT function, then it will check the box.

kerwin

Thank you for the help, Paul! That makes sense to use MAX and COLLECT. 

When I added the formula you suggested, none of the boxes are checked though. 

I added a screenshot... 

Paul Newcome

Hmm... That's odd. Let's try breaking this down for some trouble shooting.

 

Change [Column4] to a date type. In row 1 enter

 

=MAX([Date Column]:[Date Column])

.

In row 3 enter the MAX/COLLECT portion

 

=MAX(COLLECT([Date Column]:[Date Column], [Badge ID]:[Badge ID], [Badge ID]@row))

.

Then in the [Is this a renewal?] column enter this in row 1 and dragfill down:

=IF([Date Column]@row = [Column4]$3, 1)

.

Let me know how these tests turn out (screenshots are most helpful). If they isolate the problem, cool. We will fix it and get you rolling. If they don't fix it, I have a few other tests we can run for further trouble shooting.

kerwin

Below is what happened after putting in those formulas.

Paul Newcome

Hmm..........

 

Take the formula in [Column4]3 and dragfill it through the rest of the column above and below.

 

Then change the formula in the checkbox column from 

 

[Column4]$3

to

[Column4]@row

 

And dragfill that for the rest of the column...

Paul Newcome

Ugh. Forget all of that mess... I totally jacked it up. Go back to the original formula I provided. Change

 

=IF(ISBLANK([Badge ID]@row), 

to

=IF(NOT(ISBLANK([Badge ID]@row)), 

.

I am sorry about that. I had it all backwards. Duh.

kerwin

Unfortunately, it is saying #incorrect argument with that change.

Paul Newcome

The closing parenthesis at the end are misplaced.

 

=IF(NOT(..............................................................................[Badge ID]@row, 1), "")))

 

Should be 

 

=IF(NOT(..............................................................................[Badge ID]@row)), 1, ""))

.

I had that part messed up in the original formula as well. That's what I get for moving too fast on a Friday afternoon. Sorry about that.

.

=IF(NOT(ISBLANK([Badge ID]@row)), IF([Date Column]@row = MAX(COLLECT([Date Column]:[Date Column], [Badge ID]:[Badge ID], [Badge ID]@row)), 1, ""))

 

kerwin

THANK YOU SO MUCH!!! Your time helping is very much appreciated!

Paul Newcome

Happy to help. 

SunnyB

@Paul Newcome

Hello!

I am trying to use this to just check off older duplicates but, keep getting the #Unparseable back. Can you please tell me how to correct the formula below to get that done?

I am not a syntax wiz just enough to be dangerous so any help would be wildly awsome!

Thank you?

SunnyB

Thank you!*

Paul Newcome

@SunnyB Try removing the closing square bracket after the @row in the first cell reference. The square brackets should only be around the column name the same way you have the rest of them.