Search code examples
google-sheetsgoogle-sheets-formula

Spreadsheet how to get rows of filled cells

I would like to know if there is a way to do the following in a spreadsheet (Excel, Calc, etc.).

If I start from the following spreadsheet:

    A  B  C  D  E  F  G  H  I 
-------------------------------- 
1 |       X     X  X
2 |    X        X     X     X
3 | X                               
--------------------------------

I would like to be able to get the columns that contain some value in the range that I have passed. For example:

Function(A1:I1) = {C,E,F}
Function(A1:I1) = {B,E,G,I}
Function(A1:I1) = {A}

Can you think of any ideas to get what I need? Any ideas would be greatly appreciated.

Solution

  • try:

    =ARRAYFORMULA(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(B2:J10="",,B1:J1&",")),,9^9))), ",$", ))

    enter image description here