Duplicates - Check mark all occurrences except the last/newest one
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)
Answers
-
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.
-
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...
-
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, ""))
-
THANK YOU SO MUCH!!! Your time helping is very much appreciated!
-
-
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?
-
Thank you!*
-
@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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives