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

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.

CodePudding user response：

=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 <> '×'", ))

=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", ))