gs vlookup - Google Sheets transform variable width/height sheet into list with ARRAYFORMULA -

i can't life of me figure out how create generalized formula convert following:

name  | cool | smart | funny | ... | bill  |      | x     | x     |     | sally | x    | x     |       |     | bob   | x    |       | x     |     | cindy |      |       |       |     | steve | x    |       | x     |     | carol |      |       | x     |     | bob   |      | x     |       |     | ...   |      |       |       |     | 

into this:

bill:smart,funny sally:cool,smart bob:cool,funny cindy: steve:cool,funny carol:funny bob:smart 

i'd able work arbitrary number of columns , rows. there way achieve without need "dragging?" i.e. arrayformulas.

here link example google sheet.

well, bear of hack, works. work arbitrary rows , arbitrary columns, without formula dragging.

=arrayformula(regexreplace(regexreplace(transpose(split(concatenate({data!a2:a&":", regexreplace(data!b2:o, "x", regexreplace(data!b1:o1, "$", ",")), if(len(data!a2:a)=len(data!a2:a), "|")}), "|")), ",$", ""), "^:$", "")) 

a few caveats:

• there must not unrelated data below or right of start of data table (i.e. except header rows or columns, data must in sheet itself.
• the names , labels must not contain "|", it's used internally split character (this can changed character)
• the data can't extend past given column. in formula above, maxes out @ column "o", can arbitrary column (column "zz", example)

i put formula work - can view spreadsheet clicking here

the "result" sheet has formula , output wanted.