2

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

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

  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.

2 Answers 2

5

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

enter image description here

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.

enter image description here

2. Add data to the PivotTable

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

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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

enter image description here

5. Cleanup as desired

enter image description here

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:

enter image description here

3

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

enter image description here

2
  • I have Excel 2016 (maybe Excel 2013 on some other computer). It seems that UNIQUE is exclusive to Excel 365 so far. I probably can try Excel 365 for free.
    – Taladris
    Jul 27, 2021 at 14:56
  • 1
    your MAXIFS can spill if you do: =MAXIFS($B$2:$B$7,$A$2:$A$7,E2#) and if you do SUMIFS instead of FILTER it will spill also: =SUMIFS($C$2:$C$7,$A$2:$A$7,E2#,$B$2:$B$7,F2#) Jul 27, 2021 at 17:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .