I have a table like so;

Component | Time  
1         | 2014-03-13 13:52:12
1         | 2013-05-20 12:20:20
2         | 2014-03-13 13:53:43

etc., etc.

There can be x times for a single component. i.e. Component 1 has 2 times, Component 2 has 1 time, Component 3 might have 5 times.

I can used the Group function to group by component, what I really want though, is to just have the most recent time for each component. Not sure if there's a function to do something like this? Or I'll need to make something myself?


You can create a Pivot Table based on the data.

Select the table containing all those data (or simply $A:$B in your case). Note that the first row need to be the column label. From the ribbon, select Insert > PivotTable and then click OK as follows:

Insert Pivot Table dialog

Next you will see a blank Pivot Table template with all column labels on the right panel. Drag the label to be grouped (i.e. Component for your case) to "Row Labels". Drag "Time" to the "Values" box.

How to drag fields to Pivot Table

You will see "Count of Time" appears, indicating that the Pivot Table currently count the number of times "Time" appears per group. To change it to the latest time (i.e. Maximum value for each group), click on "Value Field Settings" at "Count of Time" (as below picture), and change the "Summarized by" into "Max".

Change the summarize by to Max

And the final results looks like below:

Final pivot table results

Note that the pivot table created may not refresh automatically; to manual refresh the table, click on the Pivot Table, on the ribbon choose PivotTable Tools > Options > Refresh.

  • A little more fiddly than I was hoping, but it is what I was after. I really appreciate the screenshots too. Thanks. – Trent Mar 13 '14 at 7:36
  • Pivot tables are incredible powerful tools that simplify data presentation. They're very much worth learning how use. – JaredT May 9 '16 at 1:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.