Nov 10 2022 05:07 AM
Hello,
Horrible title, sorry.
I am looking for a way to find respondents with multiple answers to a question. An example below:
Respondent | Answer |
a | 1234 |
a | 1234 |
a | 12345 |
b | 456 |
b | 456 |
c | 12345 |
c | 12345 |
In this case I would like to identify that respondent "a" have two different answers - 1234 & 12345. Then print all "a" rows to a new sheet, in order to identify these.
"c" has the same value as in "a", as that would happen for us.
Is there any way to do what I am looking for?
Any pointers are greatly appreciated.
Many thanks!
Nov 10 2022 07:59 AM - edited Nov 10 2022 08:00 AM
Here's a potential solution if you're using Excel 365:
Go through each row and FILTER by respondent. Count the UNIQUE answers being returned. Next, we filter the data for all rows where 2 is present (Returned from BYROW). The 'b' is assigned to results of the BYROW. UNIQUE is then used so no duplicate rows are returned.
=LET(b,BYROW(resp,LAMBDA(row,COUNTA(UNIQUE(FILTER(answer,resp=row))))),UNIQUE(FILTER(A2:B8,b=2)))
Nov 10 2022 11:03 AM
An alternative could be Power Query. The blue table is a dynamic table where you can add all data. Then you can click in any cell of the green table. Then right-click with the mouse and refresh.