'Google sheet: joint text from multiple columns
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 |
Solution 1:[1]
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)))))
Solution 2:[2]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | MattKing |