Pivot Table Design

[Ohio Tournaments]

This is follows up an earlier post on a point system for youth wrestling results that I am working on for my site. Thanks to the gracious help of David A Coop on this great site, I was able to create a system which automatically assigns points to wrestlers for placing at a tournament, based on what their place was and how many wrestlers were in the weight class.

The final step in this process is that I need to create a pivot table, ranking the wrestlers by total points within their age division and weight class- i.e. I'd like to see the Top 25 wrestlers at 9-10 Rookie 55 lb. weight class, 9-10 Rookie 60 lb. weight class, 9-10 Rookie 65, and so forth. The wrestlers should be listed in order of points within each age group and weight division. The posting of results is going to be an on-going process, so I need to design the pivot table in such that I can append the result and have it automatically update the Pivot Table.

I have attached the document as I have it currently. I know nothing of pivot tables, and have found the tutorials confusing to say the least. Thanks in advance for any assistance!

[David A Coop]

OhioT Project Jan 20th with PT.xlsx

Hi Mike,

Pleased to see you are making progress.

Have a look at this! I have had to extend the helper columns for the Pivot Table because a PT works needs to analyse the data like this. You have to get rid of the blank cells, so my formula says if the cell to the left is blank, get the value above ME. This fills up a table that can be 'pivoted'.

I have added a couple of suggestions.

The PT in column A has TWO filters to select - Age and Weight. You can select anything you want. (I have also included # of Wrestlers and Place). You can drag fields in or out of the table as you please. (Note: Left click on the field in the Row Labels area and set Field Settings Subtotals to None if you want to get rid of them).

In column F there is another version of the same PT. This time I am showing a couple less fields, but instead of filtering on the Weight, I have moved it onto the Row Labels area. (Have a look at the Pivot Tables Field List on the right of your screen).

Because Weight is TEXT (not numeric), you will get unexpected sort characteristics.

Also note that in the Age filter, you can see Multiple Items. This is because you have 9-10 Rookie and 9-10 ROOKIE - two different spellings. (There is also a 9-19 Rookie which I suspect is a typo). You really need to clean this sort of thing up as Excel can only report on what it sees, not on what it should be.

Pivot Tables are very powerful, Mike, when you use them correctly, so I would encourage you to persevere. Experiment with the samples I have included.

Try, for example, selecting any cell in the PT in col F and dragging Weight from the Row Labels box on the right to the Column Labels box, and watch what happens. (This will give you a clue as to why it's called a Pivot Table.

Regards,

David




When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Ohio Tournaments]

David:

Awesome stuff. I will get to work on cleaning up the document, such as the 9-10 Rookie and 9-10 ROOKIE problem and non-standard weight classes, and probably have more questions once I cross that bridge lol. Thank you so much once again!!!

Mike

[David A Coop]

You're welcome Mike, thanks for the positive feedback.

Please remember to close the thread when you're satisfied, and click the * Add Reputation fro posts which have helped.

David


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Ohio Tournaments]

Didn't want to close the thread just yet, because I may want to re-visit an unforeseen issue once I finish cleaning up what I have now. I already added to your reputation, tried to do so a second time actually and it wouldn't let me- said I had to "spread the wealth" of reputation bolster or something to that effect lol.

[David A Coop]

Didn't want to close the thread just yet, because I may want to re-visit an unforeseen issue once I finish cleaning up what I have now. I already added to your reputation, tried to do so a second time actually and it wouldn't let me- said I had to "spread the wealth" of reputation bolster or something to that effect lol.

Thanks Mike. Wish there was some wealth! I just do this for fun.

DAC