I have data in an Excel worksheet. To simplify a bit, there are three columns:
- the first one (column A) is the name of an individual
- the second column (column B) is the date a performance was measured.
- the third one (column C) is the performance (numerical value).
I would like to extract from this worksheet the most recent measure of the performance of each individual.
How can I do this using Excel?
Ex of data (I don't know how I can provide a MWE):
A | B | C |
---|---|---|
Alice | 6/25/2021 | 14 |
Alice | 7/14/2021 | 21 |
Alice | 7/25/2021 | 24 |
Bob | 7/13/2021 | 12 |
Charly | 7/24/2021 | 65 |
Charly | 7/25/2021 | 67 |
The desired result is the last performance of each individual:
A | B | C |
---|---|---|
Alice | 7/25/2021 | 24 |
Bob | 7/13/2021 | 12 |
Charly | 7/25/2021 | 67 |
I tried to use a filter (Data -> Filter), especially a Date Filter like "this week" or "last week", but there are a lot of disparities in the date and the given filters would all miss values. I cannot do it by hand as there are about 20,000 rows.
Some properties of my data (if necessary, but I think a general answer would be better):
- The worksheet is organized by lexicographic order: dates are in increasing order and performances are in increasing order for the same date and individual.
- As far as I can tell, an individual's performance was measured at most once each day.
- Different individuals may have a different number of performances.
Note: I was not sure how to title this question, for lack of appropriate keywords. For the same reason, my research for similar questions was sub-par. Feel free to edit and/or point out to similar questions.