Matching almost all cases from multiple columns

by Zev   Last Updated May 15, 2019 18:01 PM

Highlighted row is data I want to compareI 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

Cheers



Answers 2


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

Selkie
Selkie
May 15, 2019 15:27 PM

Another method using SUMPRODUCT:

this will check if there are six sequential answers that are the same anywhere in a row between columns T and AI:

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

Using equalities to generate true (1) or false (0) results you can then run them through the SUMPRODUCT.

Edited: a brief explanation also notice a problem in the formula and fixed it

cybernetic.nomad
cybernetic.nomad
May 15, 2019 15:38 PM

Related Questions





Select 1st day of each user id in excel

Updated December 19, 2018 07:01 AM

Text function in Excel isn't formatting year

Updated February 07, 2019 07:01 AM