Google Sheets query with special transpose

Question

I'm struggling to write a query that appears more complex than anticipated, here a sample of the data to process

CW6	CW7	CW8	CW9
A	B	C	A
B	D	E	B

Here the result expected at the end:

Item	CW
A	"CW6 CW9"
B	"CW6 CW7 CW9"
C	"CW8"
D	"CW7"
E	"CW8"

Any idea how to achieve this ? What I've tried in many steps:

=transpose(query(<data>,"Select *",1)) to transpose my data

Then I used

=transpose(
     query(
        transpose(<data transposed>),,9^9
     )
)

to join all columns

Then I select all the unique values I want to retrieve the column header (CWxx)

=query(
     unique(flatten(<Data>)),
     "Select Col1 where Col1 is not null"
)

Finally I join the data that were transposed to get it in one string (ie 1 column):

=JOIN(" ", QUERY(< data transposed and joined>,"SELECT Col2 WHERE Col1 contains '"&A1&"'",0))

At the end I got the wanted array but I have to copy the last join formula on each line what I do not want to do as my values are dynamic

Any idea how to achieve this (or a simpler way to avoid all these steps) ?

Answer 1

try:

=ARRAYFORMULA({QUERY(SORT(UNIQUE(FLATTEN(A2:D))), 
 "where Col1 is not null"), TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(
 IFERROR(SPLIT(FLATTEN(IF(A2:D="",,A2:D&"×"&A1:D1&"×"&A1:D1)), "×")), 
 "select max(Col3) where Col2 is not null group by Col1 pivot Col2"), 
 "offset 1", 0)),,9^9)))})

or:

=ARRAYFORMULA({QUERY(SORT(UNIQUE(FLATTEN(A2:D))), 
 "where Col1 is not null"), """"&TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(
 IFERROR(SPLIT(FLATTEN(IF(A2:D="",,A2:D&"×"&A1:D1&"×"&A1:D1)), "×")), 
 "select max(Col3) where Col2 is not null group by Col1 pivot Col2"), 
 "offset 1", 0)),,9^9)))&""""})

---

update:

=ARRAYFORMULA({QUERY(TO_TEXT(SORT(UNIQUE(FLATTEN(A2:D)))), 
 "where Col1 is not null", 0), TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(TO_TEXT(
 IFERROR(SPLIT(FLATTEN(IF(A2:D="",,A2:D&"×"&A1:D1&"×"&A1:D1)), "×"))), 
 "select max(Col3) where Col2 is not null group by Col1 pivot Col2"), 
 "offset 1", 0)),,9^9)))})