2

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?

1 Answer 1

3

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.

2
  • 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, 2014 at 7:36
  • Pivot tables are incredible powerful tools that simplify data presentation. They're very much worth learning how use.
    – JaredT
    May 9, 2016 at 1:35

You must log in to answer this question.

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