Eliminate multiple older dates from a spreadsheet

[Tracy7212]

My sheet has ID numbers in Column A and dates in Column B.  When there is more than one date for an ID number, the ID number is repeated in Column A with a different date each time in Column B.  (Some ID numbers have only one date, but most have two or many dates.)  I want only the latest date for each ID number.  Is there a way to do this in Excel?  Thanks

[#2]

@Tracy7212 

Yes.

But that isn't really a sufficient answer, since there are other questions that would need to be asked of you and your spreadsheet. Is it possible for you to post a copy of the spreadsheet (so long as there's no confidential info)?

For example, since there are for many IDs, many rows with multiple dates, and you only want the latest date for each ID, does that mean

• you just want to see what the latest date is for any given ID?
• you want to eliminate the rows that have earlier dates?
• you want all the earlier dates replaced by the latest date?
• you want to place blanks where there were earlier dates?

And, frankly, as a person who during my working career (retired now) had responsibility for a major corporation's HR database, I know that there can be real value--in some cases essential--in retaining history of events or transactions. For what it's worth, the latest date for any given ID can always be ascertained with judicious use of the MAX function.

[#3]

Good questions! I'm making a simple cheat-sheet of data queried from a proprietary database (unfortunately I couldn't accomplish this within the DB) so there is no worry of loss of historical data. I want the ID number in Column A to appear only once, with its most recent date in Column B. I'll post an abbreviated version: Columns A & B is the dataset, Columns D & E show blank rows where the eliminated data were, and Columns G & H is the same, with the blank rows removed.

[#4]

@Tracy7212 

[#5 · Accepted solution]

@Tracy7212 

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

[#6]

Thank you SO MUCH for this help.  I wish I knew these more sophisticated functions, and I'm grateful there are folks as yourself willing to lend a hand.  Your guidance was crystal clear, and I haven't even looked at the video yet.  I would just add for anyone else using this technique, at the end, you may need to copy and paste-as-values Columns L & M, and choose a date format for Column M.  

My actual spreadsheet had over 3,000 rows, so, again, my great appreciation!  Tracy

[#7]

@Tracy7212 

An alternative formula?

= LET(
  distinctID, UNIQUE(ID),
  maxDate, MAXIFS(Date, ID, distinctID),
  IF({1,0}, distinctID, maxDate))

[#8]

Maybe, I'm missing something but...

If you can query the database, the below SQL will give you what you want.  You can also use drag-and-drop in MS Query to get Excel to build the query for you.

select id, max(date_of_interest)
from my_table
group by id

@Tracy7212 

[#9]

@tusharm10 

If that's SQL database better to use Power Query, it generates in background same SQL query using query folding mechanism, no need in native SQL query.

[#10]

You are very welcome. I discovered those functions only late last year...and they've been great, as you're finding out