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

Question

I can't for the life of me figure out how to create a generalized formula to convert the following:

Name  | cool | smart | funny | ... |
Bill  |      | x     | x     |     |
Sally | x    | x     |       |     |
Bob   | x    |       | x     |     |
Cindy |      |       |       |     |
Steve | x    |       | x     |     |
Carol |      |       | x     |     |
Bob   |      | x     |       |     |
...   |      |       |       |     |

Into something like this:

Bill:smart,funny
Sally:cool,smart
Bob:cool,funny
Cindy:
Steve:cool,funny
Carol:funny
Bob:smart

I'd like it to be able to work for an arbitrary number of columns and rows. Is there any way to achieve this without the need for "dragging?" I.e. with ARRAYFORMULAs.

Here is a link to an example Google Sheet.

See [Concatenate top row cells if column below has 1](http://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1/28680713#28680713). — , Jan 24 '16 at 17:47

Brionius · Answer 1 · 2016-01-25T02:05:38.030

3

Well, this is a bear of a hack, but it works. It will work for arbitrary rows and arbitrary columns, without any 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 be any unrelated data below or to the right of the start of the data table (i.e. except for extra header rows or columns, the data must be in a sheet by itself.
The names and labels must not contain "|", as it's used internally as a split character (this can be changed to another character)
The data can't extend past a given column. In the formula above, it maxes out at column "O", but that can be any arbitrary column (column "ZZ", for example)

I put the formula to work - you can view the spreadsheet by clicking here

The "Result" sheet has the formula and output that you wanted.

edited Jan 25 '16 at 02:05

answered Jan 24 '16 at 18:23

Brionius

13,858
3
38
49

Ah, that's amazing. Thank you @Brionius. Do you have a sense of why it's not possible to add another `ARRAYFORMULA` to `=JOIN(",", ARRAYFORMULA(SUBSTITUTE(FILTER(Sheet1!$B$1:$1, EQ(Sheet1!B2:2, "x")), "@", "")))`? Or rather I have trouble understanding where the `ARRAYFORMULA` does and does not apply... – Aaron Gibralter Jan 25 '16 at 00:26
1

If I understand your question correctly, you're thinking that each application of `ARRAYFORMULA` might "extend" the formula in a new dimension. As I understand it (and I don't understand it particularly well), that's not the case - wrapping a formula in an `ARRAYFORMULA` just tells the spreadsheet to iterate over the array arguments in some predefined way. Adding a second `ARRAYFORMULA` wrap is redundant, and has no effect. You have to rely on hacks with `SPLIT` and `CONCATENATE` and the like if you want to make formulas more flexible than that. – Brionius Jan 25 '16 at 02:09

score 0 · Answer 2 · edited May 23 '17 at 10:28

hope, this works:

=ArrayFormula(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(A2:A8&": /")),"/"))&SUBSTITUTE(trim(transpose(query(transpose(IFERROR(ArrayFormula(HLOOKUP(REGEXREPLACE(B2:D8,"x","1")*1*COLUMN(B2:D8)*row(B2:D8)^0,{COLUMN(B2:D8);B1:D1},2,0)),"")),,counta(B1:D1))))," ",", "))

my sample in action =)

Update

I figured out how to make the formula shorter:

=ARRAYFORMULA(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(A2:A8&": /")),"/"))&SUBSTITUTE(trim(transpose(query(transpose(REPT(B1:D1,B2:D8="x")),,counta(B1:D1))))," ",", "))

The formula above work's the same. It uses the trick, proposed here.

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

2 Answers2