Matching almost all cases from multiple columns

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

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

Cheers

Tags :

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=F1`from 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
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