Looking for a way to identify respondents with multiple different answers

Occasional Visitor

Hello,

 

Horrible title, sorry.

 

I am looking for a way to find respondents with multiple answers to a question. An example below:

 

RespondentAnswer
a1234
a1234
a12345
b456
b456
c12345
c12345

 

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!

2 Replies

@ehand_ 

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)))

 

 

@ehand_ 

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.

Respondent Answer.JPG