Survey Questions Excel-Highest Occurence, Second, Third, etc.

common763

New Member
Joined
Feb 5, 2019
Messages
1
Might be missing the obvious but need some help. I have this data that I extract from a SharePoint survey and dump into excel. The questions all have various responses and the report is becoming tedious to maintain. I want to pull the highest response, second, third for each question. When I use the below formula it only works if the data range is exact.

=IFERROR((INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10),0)+{0,0})),"") for Highest

=IFERROR(INDEX(A$1:A$10,MODE(IF(COUNTIF(H$1:H1,A$1:A$10)=0,MATCH(A$1:A$10,A$1:A$10,0)+{0,0}))),"") for second,etc.

I cant dump this data into a pivot table because one question has yes/no, the next ratings 1-5, etc. Is there a better approach? If not is there a way to automate this so I can use something similar to the formula that would prevent me from having to reset the data range each month because that would take forever? I have three separate surveys to track with about 60 total questions.

If i am not explaining properly...I select say A1:A100 and data only is present in A1:A10 the formula above doesnt work.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top