Pivot table, most recent dates, item with highest score

[Adam_C]

I have a data set with the following fields

Date
Member (3 specific members)
Game (fixed list of 20 games)
Score (integer)


I want to use a pivot table to show which out of 2 of the members had the highest score in each game played at the most recent date.

(Incidentally the games are all always played on the same date)

Ideally, the pivot table could be refreshed when the data is updated and not require any further manual manipulation.

Is this doable?

 

[jakeman]

I have a data set with the following fields

Date
Member (3 specific members)
Game (fixed list of 20 games)
Score (integer)


I want to use a pivot table to show which out of 2 of the members had the highest score in each game played at the most recent date.

(Incidentally the games are all always played on the same date)

Ideally, the pivot table could be refreshed when the data is updated and not require any further manual manipulation.

Is this doable?

Well, it's doable but not without some automation and some additional steps.

First off, if you are constantly appending new data to your data set, then it's best to create a dynamic range and store it as a Named Range. If your data is only the four columns: Date, Member, Game and Score then I would create a named range using the following:

=Sheet1!$A$1:INDEX(Sheet1!$1:$65535, COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))

Take this formula and go to Insert > Name > Define. Give your range a name and then paste the formula into the Refers to: section. Click ok. Now you've stored your range which you can then refer to in your Pivot Table range.

Secondly, it would require that you create a macro to automate both the refresh of your pivot table and the selection of the most recent date. Automating the refresh is just this simple code:

    Sub refresh_pivot()
'
'
    ActiveSheet.PivotTables("High Scorer").PivotCache.Refresh
 
End Sub

but in order to select the most recent date of your choosing, you'd need to create code to account for all of the various pivot items in your list and that's a little more time consuming. Because you would have to show as visible false, all of the pivot items you do not want to see for both the date field and the other member you do not want to see in the results.

Maybe if I have time I can help with that. How familiar with macros are you and if so, do know know how to create a form button to click on for both pivot table refresh as well as filtering the records you want to see?