CODEWITHSUNDEEP
google-sheetsformattingconcatenationmultiple-columnstextjoin
ubundom
ubundom

Reputation: 175

Is there a neat way of formatting a 3 column array in Google Sheets?

Using Google Sheets, I would like to save space, when I render the output from a filter and query, by placing the key value from "Dept" in column 1 as a header over "Member" and "Code" in columns 2 and 3.

Thus far I have only found that I can achieve this with tedious manual copying and some conditional formatting. I wonder if there might be way using inbuilt Google Sheets functions but, I guess that I might have to write an Apps Script.

Table Transformation

Herewith a link:

https://docs.google.com/spreadsheets/d/1SUcqfudwZuXasFHKxXbiTns9s2fvCKJAtUVk8LuisSQ/edit?usp=sharing

I want to create Table E. I am struggling with trying to join the two input tables A & B on the "Member" field to form Table C. I can get the result with several messy steps in between but have failed to find an efficient, single formula method with multiple attempts using ARRAYFORMULA(), TRANSPOSE(), SPLIT() and TEXTJOIN() in various combinations.

The following image is a refinement of that which I posted earlier.

enter image description here

Many thanks for your help! Here is an answer thanks to Matt King:

=sort(query(ARRAYFORMULA(SUBSTITUTE(QUERY(SPLIT(FLATTEN(E5:K5&"|"&IF(E6:K12="","#",E6:K12)),"|",0,0),"where Col2 <>''"),"#","")),"select * where Col2 is not null"),1,1,2,1)

Upvotes: 1

Views: 334

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN({IF(
 COUNTIFS(A2:A10, A2:A10, ROW(A2:A10), "<="&ROW(A2:A10))=1, A2:A10, ), 
 B2:B10&"×"&C2:C10}), "×")), "where Col1 is not null and Col1 <> '×'", ))

enter image description here

=ARRAYFORMULA(QUERY({IFNA(VLOOKUP(IF(F2:F15="",,ROW(E2:E15)), 
 FILTER({ROW(E2:E15), E2:E15}, F2:F15=""), 2, 1)), E2:F15}, 
 "where Col3 is not null", ))

enter image description here

Upvotes: 2

Related Questions