Forum Discussion
Eliminate multiple older dates from a spreadsheet
- May 26, 2021
You will need the most recent release of Excel in order for this particular solution to work, but it's worth getting if you don't have it.
I've used two of the new Dynamic Array functions in this solution, UNIQUE and FILTER. Down below I'll put a link to a YouTube video that explains the functions.
In cell L2 I entered this formula: =SORT(UNIQUE(A2:A21))
That formula, in that single cell, produced the list of unique IDs, sorted in order.
Then in Cell M2, I entered this formula (and then copied it down adjacent to the unique ID list:
=MAX(FILTER($B$2:$B$21,$A$2:$A$21=L2))
Which, first, by means of the FILTER function, gets all of the dates associated with whatever ID is adjacent (L2 in the first row)
and then, by means of MAX function, picks only the greatest date.
The result is exactly like your sample showed it should be. See the attached.
Here's that promised link to YouTube: https://www.youtube.com/watch?v=9I9DtFOVPIg
An alternative formula?
= LET(
distinctID, UNIQUE(ID),
maxDate, MAXIFS(Date, ID, distinctID),
IF({1,0}, distinctID, maxDate))