Looking for a way to identify respondents with multiple different answers

[ehand_]

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!

[#2]

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

[#3]

@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.