I am trying to measure the validity of questionnaire responses in excel. One of my tests is checking whether the person answering it just chose the same response for many questions in a row. I am trying to detect a pattern for 6 questions where the possible responses are 1-6. I am not looking for a case where all the responses are a specific number, but for any case where all 6 responses are the same eg all 5's all 4's etc.
The responses to the questions are located in columns next to each other, ie C, D, E...H and each respondent is in their own row.
Additionally I would like to see the cases where 5 of the 6 responses are the same, because I believe there is some likelihood that a person might just select a single answer differently to make it seem more believable. My guess is if this problem can be solved for 5 out 6 cases, 6 out 6 is more straightforward
I can share my current failure which is using if statements and/or countifs to try and match a=b, b=c and so forth. One article which describes this way is Compare two columns for matches and differences
I am open to solutions with vba, as I have used it a bit in the past, but it would have to be fairly step-by-step, I'm coming back to excel after not using it for a while
A really long If/And/Or statement will do it.
=if(Or(And(A1=B1,B1=C1,C1=D1,D1=E1),And(B1=C1,C1=D1,D1=E1,E1=F1).....),"Lots of things in a row match","Not a lot of things in a row match")
It gets easier as you go along, since you can copy most of the previous
AND statement into the next one, then just write the next comparison. For example, for the 3rd
AND you just need to copy
C1=D1,D1=E1,E1=F1from the previous
AND, then add in
F1=G1 at the end. Rinse and repeat.
It gets much easier if you throw the data into an Excel table as well
Another method using
this will check if there are six sequential answers that are the same anywhere in a row between columns
=IF(SUMPRODUCT(--(T2:AC2=U2:AD2),--(U2:AD2=V2:AE2),--(V2:AE2=W2:AF2),--(W2:AF2=X2:AG2),--(X2:AG2=Y2:AH2))>0,"six in a row","")
Here is an explanation of
Using equalities to generate true (
1) or false (
0) results you can then run them through the
Edited: a brief explanation also notice a problem in the formula and fixed it