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

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:

enter image description here

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

enter image description here

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? – brianfit Feb 14 '20 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... – MattKing Feb 14 '20 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

enter image description here

  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

enter image description here

Test sheet

  • 1
    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. – brianfit Feb 14 '20 at 7:32
  • No worry, @MattKing's answer is obviously better :) – pjmg Feb 14 '20 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.