Transpose row of irregular number of values to columns of matching indices

I have a Google Form featuring an ID number and a checkbox field capable of generating multiple choices, anywhere from 1 to 4.

When multiple checkboxes are ticked, the output goes into a single field with comma separators. I've created a second sheet in which I split that data using `Split('TAB_REFERENCE!A1',"TRUE, TRUE)` into a sheet that looks like this:

I now want to query that data to display `ID` numbers associated with each of the elements, to look like this:

I've tried multiple boolean `OR` statements in a filter, Pivot tables, and a half dozen bad ideas.

I can't use ArrayForumula because the element count varies. I know this is solvable but as my headline suggests, I can't even articulate the problem succinctly enough to get good search results. It's like a transpose of part of a row into columns of unique values with the index value displayed under the columns it matches. Help!

I think this relatively simple single formula can do it without a separate tab to do the splitting. I also made a sample sheet here. On the MK.Help tab, you'll find this formula in cell B2.

``````=ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(IF(REGEXMATCH('Form Responses 1'!C2:C5,B1:E1),'Form Responses 1'!B2:B5,),,9^9))," ")))
``````

Hopefully you can figure out which references correspond to the correct ones on your real sheet.

• BOOM! What kind of sorcery is this?????? I can vary the field assignments and watch it work, but I can't parse why. Transpose. Then Split. Then transpose again based on a match. AH, I see it now as I follow the bouncing ball, but I would never in a million years have been able to construct that. Thanks for the wizardry. And out of curiosity, what's the 9^9 doing to the regexmatch? Commented Feb 14, 2020 at 7:41
• Glad it worked for you! the 9^9 is actually not part of the regexmatch, it's in the query() 9^9 is just an arbitrarily large number 9 to the 9th power, what it does is concatenate all the output from the IF(REGEXMATCH()... ) . the query function automatically concatenates as many "header" rows as you specify in a given array. this allows me to bunch all the IDs together, so they can get split back out, thus getting rid of the problematic spaces... Commented Feb 14, 2020 at 19:16

You can do it with several simple formulas if you can use multiple steps :

1. first you need to transpose the data

1. then add a row with the animals
2. use this formula `=IF(COUNTIF(H\$2:H\$5,\$G8),H\$1,)` (`\$` are very important for easy formula-dragging)
3. transpose a second time
4. you're done

• Thanks!!!!! This works, and it meets the needs I laid out in the question, but it has the disadvantage of the results being scattered down the columns with gaps. My bad, I failed to note one of my goals was to have a contiguous set of results beneath the ID (there's a cut and paste operation I'm trying to do on the data that won't tolerate blanks). Another operation to filter the blanks would have nailed it, but @MattKing's answer does everything in one formula. Commented Feb 14, 2020 at 7:32
• No worry, @MattKing's answer is obviously better :)
– pjmg
Commented Feb 14, 2020 at 7:44