I have data in an Excel worksheet. To simplify a bit, there are three columns:

1. the first one (column A) is the name of an individual
2. the second column (column B) is the date a performance was measured.
3. 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):

The desired result is the last performance of each individual:

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):

1. The worksheet is organized by lexicographic order: dates are in increasing order and performances are in increasing order for the same date and individual.
2. As far as I can tell, an individual's performance was measured at most once each day.
3. 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.

Solution 1:

If you don't have access to the UNIQUE function, you can use a PivotTable to generate the desired result. There are a few idiosyncrasies with PivotTables but it'll get the job done.

1. Create the PivotTable

Click anywhere in your data and, on the Insert ribbon click PivotTable

Make sure the range is your entire table and click OK. The range may be wrong if you select a range of cells in the table instead of just one or if there are blank rows or columns in your table. You can correct the range by clicking the up arrow button on the right and selecting the correct range.

2. Add data to the PivotTable

You should now have a new worksheet with a blank PivotTable.

On the right, click and drag "Name" and "Date" from the list at the top to the "Rows" section at the bottom. Then drag "Date" and "Performance" to the "Values" section.

3. Format the PivotTable

Right-click somewhere in the PivotTable and click on "PivotTable Options..." from the menu. In the "Display" tab, check "Classic PivotTable layout" and then click OK.

Right-click on of the months in the "Months" field (should be column B) and click "Ungroup..." in the menu that pops up. The table should now look like what you see below.

4. Filter out the data you don't need

Right-click on one of the names in the table and click on "Field Settings...". In the "Subtotals & Filters" tab, choose "None" under Subtotals and click OK.

Right-click on one of the date values, probably in column C and probably called "Count of Date", then hover over the "Summarize Values By" submenu and click on "Sum". The dates will show as numbers (6/25/2021 = 44372) but you can ignore that for now.

Select one of the dates in the Rows section of the PivotTable, probably in column B and called "Date". Click the drop-down arrow at the top of that section, hover over the "Value Filters" submenu, and click on "Top 10..." at the bottom of that list.

Change "10" to "1" and click OK.

5. Cleanup as desired

You can hide column C. You could turn off the "Grand Total" row. You can turn off the expand / collapse buttons next to the names. You can rename column D to "Performance " (but not "Performance" with no space at the end because that name is already reserved for the data field itself). For an explanation of how, I'll let you search yourself for more general instruction on how to use PivotTables. My mockup looks like this now:

Solution 2:

If you have Excel 365, you can use these formulae:

• name: =UNIQUE(A2:A7)
• date: =MAXIFS($B$2:$B$7,$A$2:$A$7,E2)
• score: =FILTER($C$2:$C$7,($A$2:$A$7=E2)*($B$2:$B$7=F2))