当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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.

网友答案:

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.

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: