Transpose and Pivot data for a list from columns

Hello,

I have a survey out on surveymonkey and the results are sent to me in the format like in the "Master" tab of the attached document (column A - AI)

What I want to do is take these responses, and transpose them into a nice list like in the "List" tab. What I've done is ran a logical test in columns AJ and AK of the responses in the "Master" tab and use these two to anchor my list in the "List" tab.

One tricky nuance, the McKinney option allows the user to select two responses so I don't get a distinct list!

Any advise here?

Thank you!

Originally Posted by Glenn Kennedy

Maybe something like this:

In a2: =IF(MASTER!A3="","",MASTER!A3)

In B2 =IF($A2="","",LOOKUP(2,1/(MASTER!C3:J3<>""),MASTER!$C$2:$J$2))


In C2: either:

=IF(A2="","",MID(TRIM(SUBSTITUTE(LOOKUP(2,1/(MASTER!J3:AI3<>""),MASTER!$J$2:$AI$2),B2,"")),3,255))

or

=IF(A2="","",LOOKUP(2,1/(MASTER!J3:AI3<>""),MASTER!$J$2:$AI$2))

depending on which you prefer.

This would work however Jordan Smith technically has 3 responses. That’s where it gets complicated.

Thanks!