Software

A quick way to return the latest date in a subset in Excel

Identifying the latest entry for a subset doesn't have to be difficult - use a pivot table.

The simple sheet below tracks units sold by personnel and date. Not too long ago, I showed you a conditional format formula that highlights the maximum and minimum values for each month. That's just one of many ways you might need to analyze your data. For instance, you might need to know the latest date each person made a sale - that's a common request.

Even if you sort the data by date or personnel, a formulaic solution will be complicated (and might take hours to build and test). The quickest solution is to avoid that approach and use a pivot table as follows:

  1. Select the data range by clicking any cell in the range and pressing [Ctrl]+[Shift]+8.
  2. Click the Insert tab and choose PivotTable from the PivotTable dropdown in the Tables group. In Excel 2003, choose PivotTable and PivotChart Report from the Data menu to launch the PivotTable wizard, which will walk you through the process. The remaining instructions are for 2007 and 2010 users.
  3. In the resulting dialog, accept the default settings by clicking OK without making any changes.
  4. From the task pane on the right, drag the Personnel field to the Row Labels list (at the bottom-right).
  5. Drag the Date field to the Values list.
  6. From the Count Of Date dropdown, choose Value Field Settings.
  7. In the resulting dialog, select Max on the Summarize Values By tab, and click OK.
  8. Select the dates in column B (B4:B7) and choose Short Date from the Number Format dropdown in the Number group (on the Home tab).
  9. To hide the grand totals row, click the contextual Design tab and choose Off For Rows And Columns from the Grand Totals dropdown in the Layout group.

A pivot table won't always be adequate, but unless you must identify each subset's latest entry within the context of all the records, the pivot table just might be the quickest and easiest solution.

To display the first entry of each subset, choose Min instead of Max in step 8 above.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
Dustdevil2169
Dustdevil2169

My project sheet, is soundling similar to yours, but the area I cant seem to get down is this. Multipule sections/offices/ entities will be updating thier respective colums of information, and what I WANT to happen is an automatic date populate in a predetermined cell. That way you can see that some value has changed. Let say that HR does daily personel attendance, they input into cells B5-B10 And Outside plant does the same in C5-C10, Houskeeping updates cells D5-D10, Under each cell (say B11 and C11 , D11) the date it was modified appear there. The formula is what I'm missing. I am not understainfg how to make this happen.

RU7
RU7

Just do a custom sort by personnel then by date. Then it is easy to pick out the last (and first if you want) entry for each person.

jbenton
jbenton

assuming data is on sheet1 and you want dates in the right format, in your summary table enter the following as an array formula (ie with ctrl-shift-enter) in cell B4 and copy down [b] =TEXT(MAX(($A4=sheet1!$B$2:$B$13)*sheet1!$A$2:$A$13),"ddmmme") [/b] this has the advantage that it doesn't need refreshing like a pivot table does to show the latest informtion

ssharkins
ssharkins

You could use a circular formula with calculation set to iteration, but that has very limited context. You could use VBA's Worksheet Change event, but that's cumbersome at best. I recommend that you create a data entry user form and let the form do the calculating. Your users might find data entry a lot easier using a form.

ssharkins
ssharkins

I generally avoid arrays, but don't mind sharing them with others. Thanks!