On a new tab, for each row, I want to enclose the text of all the columns from my dataset tab that contains the word "WORD" in its 2nd row. I cannot directly target the column letter, and the number and place of columns containing "WORD" will change over time. I've tried with HLOOKUP and QUERY, I can't get there.
Example
dataset
| # | Another header | Another header |
|---|---|---|
| xxxx | WORD | WORD |
| 1 | contentA | contentC |
| 2 | contentB | contentD |
new tab
| # | ALL WORD |
|---|---|
| 1 | contentA ContentC |
| 2 | contentB ContentD |
CodePudding user response：
use:
=FLATTEN(QUERY(TRANSPOSE(A1:B);;9^9))
or:
=INDEX(TRIM(FLATTEN(QUERY(TRANSPOSE(A1:B);;9^9))))
update:
=INDEX(TRIM(FLATTEN(QUERY(QUERY(TRANSPOSE(FILTER(
dataset!A2:99999; REGEXMATCH(dataset!1:1; "(?:)WORD")));;9^9)))))
CodePudding user response：
use:
=ARRAYFORMULA(TRANSPOSE(TRIM(QUERY(TRANSPOSE(FILTER(dataset!3:100000,dataset!2:2="WORD")),,9^9)))
the use of the number 100000 is intentional, it should be more rows than you'd ever have.