Thread: Max Function in Pivot Analysis

I have a chart of about 40,000 lines that I need to analyze. It's the results of a large survey (topic changed for privacy). Multiple users applied tags to a piece of content. For instance, if I gave you www.cnn.com and asked you to mark it as a website for: News, Media, or Entertainment, you would mark one. Multiple users did that. What I have to do for the 5,000 items that were surveyed is to figure out which answer users answered most. So in the pasted example below, for CNN there were 4 responses, and 2 people marked News. So News is the answer I want. Pivot tables give a nice layout of what those are, but I can't go through 5,000 items (like CNN.com) to manually look up the max for each. What function can I use to automate this? Any help appreciated- Thanks!

Row Labels Count of Final Answer

www.cnn.com 4

news 2
media 1
entertainment 1

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Ok, see if this helps...

First, I changed the PT layout to Tabular form so that I have separate columns for each category.

Then I applied a formula in G8 to basically put the Item name in each cell...

=IF(RIGHT(D8,5)="Total","",LOOKUP(REPT("z",255),$D$7:D8))

copied down.

Then formula in H8 to get you the most occurring Answer:

=IF(G8="","",IF(E8=INDEX($E$8:$E$23,MATCH(1,($G$8:$G$23=G8)*($F$8:$F$23=MAX(IF($G$8:$G$23=G8,$F$8:$F$23))),0)),E8,""))

which must be confirmed with CTRL+SHIFT+ENTER and copied down.

You can adjust ranges to suit your actual pivot table.

Right-click the answer field, choose Filter-Top 10 and set it to Top 1 by Count of Respondent. See attached.

An alternative formula to get the results to show up in line with first occurance of item would be:

=IF(OR(G8="",G7=G8),"",INDEX($E$8:$E$23,MATCH(1,($G$8:$G$23=G8)*($F$8:$F$23=MAX(IF($G$8:$G$23=G8,$F$8:$F$23))),0)))

Confirmed with CTRL+SHIFT+ENTER not just ENTER...

Note: You still need helper column.


P.S. Romperstomper's suggestion is a great alternative to not having to use formulas... but it lists either all your values or only your top values.... not both. But you can do 2 separate Pivots....

Sorry for having overcomplicated it... sometimes your mind bends the wrong way

Please remember to mark the thread as Solved.

Luckily I'm too simple to overcomplicate things generally!