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.
=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.
