12

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)

Comments

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

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

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.

In reply to by Paul Newcome

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... 

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.

Below is what happened after putting in those formulas.

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...

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.

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

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, ""))