EXCEL Pivot Table Question

Factual Questions
1

I have a spreadsheet. Year’s worth of data…
Column A: Date
Column B: Month
Column C: CData
Column D: DData
Column E: CData x DData = EData

I want to find the max of EDAta for each month. Not a problem, just run a pivot table. But I also need the corresponding CData and DData. Is there any way to do this with a pivot table or will I have to write a macro?

(This is a simplified version of the problem, the actual case is much more complicated)

2

You could put columns next to the pivot table you made which run a Index(range, Match(pivot table data), column) calculation. This would effectively look up the values by matching the month and max(EData) column. This would run into trouble if there were more than one row that had the max value for EData … but it’s not clear how you’d handle that anyway. (Eg. Max(EData) is 60; do you pick CData=6 and DData=10, or another row where CData = 5 and DData=12?) It’s also harder when you’re matching more than one column, but you can work around that easily enough if your raw data is in date order … I hope this gives you a direction to look in, it’s hard to be more specific without more details on your problem.

3

Thanks, I think that should do the trick, actually.

4

Glad I could help!