Pivot Table - NBA stats - Show last 5 games

[davidskg]

Hi,

I am trying to analyze NBA stats. My raw data consists of 1-2 weeks of information. During that time teams may have played 5-9 games. What I want to do is only look at the last 5 games. I have dates for each of the games, but with teams playing on different days I can't just filter to show specific dates. What I have done is added a helper column titled "Games Played". So I would filter to show only one of the stats, then filter to show one team and number it from 1 (being most recent) to however many games they have played during this time span.

Then I add that as a row label in the pivot table and filter by label to show anything 5 or less. The problem is it's a PITA having to do this for every team. Plus, when I have to incorporate new data (everyday) I have to go back through and renumber all the past ones, so that what was 1 yesterday now becomes 2...and 2 becomes 3....and so on. I hope that wasn't too confusing. Thanks for any assistance.

Raw data is structured like this:

"Stats Category" "Date" "Team" "Last Game" "Games Played"

[dilipandey]

Hi David,

Welcome to the forum.

Please post a sample file with reasonable description. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[davidskg]

The file is attached. I included data for two teams in the sample.

Another option, if any easier would be to figure out a way to just delete all games over 5 for each time, but not certain it would save any time. When looking at the file this would be equivalent to deleting data for every date older than the last 5 dates per team.

Thanks for any assistance.

NBA Stats Tables new.xlsx

[dilipandey]

Hi davidskg,

Data is little confusing for me.. you have last 3 games in column E.. then last game , home , Away, Games played etc... and I am not sure how you are arriving to these results rather I believe if you need last 5 games only, then all these columns are not required.

You need last 5 games.. and it can be extracted using column A, B and C only. If you agree, then provide just the these 3 column data and I can extract the last 5 days game results for any given team.

Regards,
DILIPandey

<click on below 'star' if this results>

[MarvinP]

Hi David and welcome to the forum,

Look at my added "Helper" Column that is inserted after sorting your raw data from Newest to Oldest Date. The helper shows how many team names are the same ABOVE the team name. Then using this Helper I did a Pivot Table with a filter of only showing Helper of 1 to 5 or the last 5 games.

See the attached for my example. I hope this helps.

[davidskg]

Thanks Marvin. I guess I am not sure the purpose of the helper column. I see it counts the number of times a team name shows up, but I don't know how it's working with the pivot table. It's still using the "Games Played" column for the filter in the pivot table. If you meant to use the helper column as the filter, I am not sure with the way it's counting that it will be what I need.

It seems to be counting every instance of "Atlanta". However, each time a team name shows up is not a separate game. It's each time a team name shows up for a different date that counts as one game. So all the times Atlanta shows up with a "date" of 1/17 that's one game. Then, when the date changes to 1/15 that will be another game. This is because each date will have 30+ stat categories associated with it. So you will see "Atlanta" with a date of "1/17" 30+ times. In your helper column all those stats for that date should have a "1" because it's the most recent game. Instead only one stat has a "1" in it.

I hope that makes sense. That's why I made that "games played" column and numbered it 1 to 8 for Atlanta. I am just trying to figure out a way to do it without having to do it manually. If there is a way to have your helper column take the date into account so it labels all 1/17 dates (in this example) for Atlanta as "1" that would work perfectly. I am just not sure how to do it.

When I added your helper column to the source data for the pivot table and filtered by it, 1-5 only showed five stats, those 5 labeled 1-5 in the helper column.

Thanks for your assistance!

[MarvinP]

Hi davidskg,

Did you change the column filter to view the different stat numbers?

[davidskg]

Hi dilipandey,

Thanks for the response. That file is the way it was extracted, which is why it had the additional columns. I have deleted those that are unnecessary for this issue. I only use the "last game" for the stats, but then each date change for each team is one game. So for instance, you see Atlanta shows up for eight different dates:

1/17/2012
1/15/2012
1/13/2012
1/12/2012
1/10/2012
1/9/2012
1/7/2012
1/6/2012

However, for each date you see in the A column 30+ different stat categories. So what I did was add the helper column "games played" and numbered every stat in column A that had a date of "1/17/2012" with a "1", then "1/15/2012" with a "2", etc. I did this for each team. Then, I used the "games played" as the filter in the pivot table to show games 1-5, which gives me the last 5 dates. Just trying to cut down the time from doing this manually.

Thanks again.

[davidskg]

Yes. As you left it, with "games played" as the filter, the stats came up. However, when I switched it to the helper column as the filter, it only shows the five associated with the 1-5. There are 227 items in the filter as the helper column labels each stat category as a separate game. Whereas it should be each change in date for each team marks a different game.

I was trying to build off what you had, thinking maybe countifs would work, but can't seem to get the formula to work and not certain it would give me what I am after anyway. Is there some formula that increments when it changes (based on team and date being the criteria), but would repeat the current count until that change?

Thanks.

[MarvinP]

Hi david,

It looks like I built the helper column and then didn't use it in my Pivot Table. I used the games played as the filter so it met your need for only looking at the "last 5" games to use for your stats. I believe the Pivot does what I thought you wanted. Perhaps we aren't on the same page with what your are looking for.

[seethesun]

Yes, my original file does what I need it to do. The problem I was having was that it's very time consuming to renumber all 30 teams as needed. I was hoping to find a formula to count this for me.

For example, in the file, 1/17/2012 is Atlanta's latest game. So it's currently numbered "1", then it increments for each older game. So 1/15 is numbered "2", 1/13 is "3", etc. However, if the have a game today (1/22), then I would have renumber all the dates so that 1/22 is numbered "1", 1/17 would then become "2", 1/15 is "3", etc.

I am just trying to figure a way to do this without having to do it manually. Another option, since I am only interested in the last 5 games, is to figure a way to delete all rows with dates older than the last 5 dates. Then, I wouldn't need the "games played" column. However, I am assuming that would take visual basic, which I know nothing about.

Thanks again.