How to combine rows into one column based on boolean values in Google Sheets?

Question

I'm having a hard time wrapping my mind around how to approach this...

Say I have a table with several items with different potential properties, which are assigned a boolean value for each property for each item. (in this case we have several options for each "color" and "size")

            |           Color                  |       Size     
-----------------------------------------------------------------------
    Item    | Red | Blue | White | Black | Tan | Large | Medium | Small
-----------------------------------------------------------------------
    Pants   |     | y    |       | y     | y   | y     | y      | y    |
    Shirt   | y   |      | y     | y     |     | y     | y      | y    |
    Skirt   |     | y    | y     |       |     |       | y      | y    |
    Socks   | y   | y    | y     | y     | y   | y     | y      | y    |

And I want to then output this into a single column like:

Pants, Blue, Large
Pants, Blue, Medium
Pants, Blue, Small
Pants, Black, Large
Pants, Black, Medium
Pants, Black, Small
Pants, Tan, Large
Pants, Tan, Medium
Pants, Tan, Small
Shirt, Red, Large
etc etc

So, I'm trying to populate a column with all the possible results of the true values for each row, concatenated with the header for each column that has a true value. In this example, it would further break out color and size for each item.

Thoughts on the best approach to this in Google Sheets?

Example sheet: https://docs.google.com/spreadsheets/d/199msbUtUuZzb0HvBMgjQW98hRt3StFEyUFfTfLzSQzw/edit?usp=sharing

Thanks for sharing this previous answer also. I was able to get this to work, but needed to add one more step to concatenate the columns into one. Your custom answer below solved that too. — Matt G
There's a hidden formula I recently discovered called =FLATTEN(). It makes these kinds of problems much simpler. Happy to demo on your sample sheet. Can you make it editable? — MattKing
So, i hadn't realized there were actually TWO datatables that needed retabulation (color and size). so Flatten() won't work. I do have another method that might be a little easier to work with, and I'll post it up. — MattKing

player0 player0 · Accepted Answer · 2020-03-23T22:23:27

on a small scale:

=ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN("\", 1, 
 IF(B3:F6="y", REPT(A3:A6&", "&B2:F2&"\", MMULT(N(G3:I6="y"), 
 TRANSPOSE(COLUMN(G2:I2)^0))), )), "\"))&", "& 
 TRANSPOSE(SPLIT(JOIN(" ", REPT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(G3:I6="y", G2:I2, )),, COLUMNS(IF(G3:I6="y", G2:I2, )))))&" ", 
 MMULT(N(B3:F6="y"), TRANSPOSE(COLUMN(B3:F6)^0)))), " ")))

for just combo of two:

=ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 IF(B3:F="y", "♦"&A3:A&", "&B2:F2&"♦", ),,99^99)),,99^99), "♦")), 
 "where not Col1 starts with ' ' order by Col1"))

modded @MK formula to mimic CSV:

=ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(QUERY(VLOOKUP(SEQUENCE(4*5*3, 1, 0)/(5*3)+3, 
 {ROW(A:A), A:A&",", IF(B:I="y", {B2:F2&",", G2:I2}, )},
 INT(MOD(SEQUENCE(4*5*3, 1, 0), {9^99, 5*3, 3})/{9^99, 4, 1})+{2, 3, 3+4}),
 "where Col2<>'' and Col3<>''")),,9^99)))

How to combine rows into one column based on boolean values in Google Sheets?

2 Answers