Excel: How to filter values in a pivot table so only rows with the latest date display

microsoft excel

I have some data I collect and I use a pivot table to analyze it. I recently started collecting the data in a different way to capture a running total, but it's messed up all my pivot tables. I am looking for a way to tame my data.

Here is what I am capturing:

Title, Score, Votes
A      41.5     11

In the past, I would update the spreadsheet if a title got more votes and the score changed. But then I realized, it would be better if I added a date, copied the row, and then updated the score.

So this:

Data, Title, Score, Votes
1/2    A     41.5     11
1/3    A     40.0      9
1/4    A     40.8     10

So now I have a vote/score history for each title.

This works good for values that only go up. For example, Date or Views (not shown in example). I can summarize these fields by Max and always get the latest value.

But my problem is that if I want to create a pivot table to see the latest scores and votes for all my titles, there is no way that I can see to do this because sometimes Scores and Votes go down. So this means that even if I summarize values by MAX value, I get an inaccurate report.

So I would get something like this:

Title, Date (Max), Score (Max), Votes (Max)  
A      1/4         41.5           11

What I am after is a way to show data in a report that only shows values from the most recent date. I am new to Excel and have been teaching myself, so I apologize if there is an obvious solution. I can't figure it out.

Best Answer

You need to add a new column to your data showing whether actual row is the latest date of the given title.

=max(if(b2=b:b,a:a,""))=a2 (supposing that your original data is in columns A-D and you put your first formula in E2) This is an array formula, so you need to hit CTRL + SHIFT + ENTER after you've typed it.

Now just add the new column as filter in your pivot and filter for TRUE.

Related Question