Pivot table issue

[potts]

Hi

I want to create a pivot table that shows the maximum weight athletes squatted in the gym each month and the maximum number of reps they did with that weight.

The raw table headings are:
NAME
DATE
SQUAT
SQUAT REPS

I have managed to group the date data to get the max weight lifted each month (pivot column headings) for the athletes (pivot row headings). However, I can't pull out the max reps they did with THAT weight for the month. If i use the "max of squat reps", I will get the max reps they did with any weight that month.

Can anyone tell me how to get the max reps of the max weight for each month?

Thank you

 

[Skybluekid]

Can you post some data?

 

[potts]

I can't post attachments, but you could create a sample by pasting the following into columns A to D:

NAME
SY
SY
SY
SY
SY

DATE
21-2-12
24-2-12
28-2-12
3-3-12
8-3-12

SQUAT
180
190
190
200
205

SQUAT REPS
7
3
4
2
2

I would want the pivot table to show max squat and associated reps for each month. I should get:
FEB squat = 190, reps = 4
MAR squat = 205, reps = 2

Thanks for your help

 

[Cofad]

I can't figure this one out either using pivot tables. Maybe you have do a more advanced search using VBA?

 

[Marcelo Branco]

Maybe something like this (not very elegant, but it works)


[TABLE="width: 362"]
<COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><TBODY>[TR]
[TD="class: xl65, width: 126"]NAME[/TD]
[TD="class: xl65, width: 55"]DATE[/TD]
[TD="class: xl65, width: 66"]SQUAT[/TD]
[TD="class: xl65, width: 100"]Max of SQUAT[/TD]
[TD="class: xl65, width: 134"]Max of SQUAT REPS[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SY[/TD]
[TD="class: xl66, bgcolor: transparent"]fev[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]190[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]190[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]mar[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]205[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]205[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
</TBODY>[/TABLE]


Row labels
NAME
DATE (group by Month)
SQUAT (Value filter ; Top 1; Max of SQUAT)

Values Area
SQUAT (Summarize by Max)
SQUAT REPS (Summarize by Max)

If you want you can hide the column SQUAT (3rd column)

Hope this is what you need

M.