Group and Max Excel Table

Question

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?

Answer 1

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:

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.

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".

And the final results looks like below:

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.