CODEWITHSUNDEEP
arraysgoogle-sheetstransposearray-formulasgoogle-sheets-query
C West
C West

Reputation: 133

Put matching column heads in one column and transpose data

I am struggling with this idea of combining columns that have the same header into one column. For example if Column B has a header "Week 1" and Column J has a header "Week 1", how can i get them to align in one column. I have been messing with queries and transposing, and I still cant get this to work.

Any help would be greatly appreciated.

Example sheet: https://docs.google.com/spreadsheets/d/1jVdziO12bsGK3kroVHl6flb6g4iOuYoOpZ5xEpSjsJs/edit#gid=0

Upvotes: 0

Views: 58

Answers (2)

JPV
JPV

Reputation: 27242

Also try

=query(ArrayFormula({split(transpose({B2:E2&"_"&A2, B6:E6&"_"&A6}), "_"), transpose({B3:E4, B7:E8})}), "order by Col1 label Col3 'Sales', Col4 'Improvement'")

and see if that helps?

Example

Upvotes: 2

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(QUERY({SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE({
 IF(B3:F4="",,"♠"&B2:F2&"♦"&A2&"♦"&A3:A4&"♦"&B3:F4); 
 IF(B7:F8="",,"♠"&B6:F6&"♦"&A6&"♦"&A7:A8&"♦"&B7:F8)}),,99^99)),,99^99), "♠")), "♦")}, 
 "select Col1,Col2,max(Col4) group by Col1,Col2 pivot Col3"), 
 "select Col1,Col2,Col4,Col3"))

enter image description here

Upvotes: 1

Related Questions