Search code examples
google-sheetspivot-tableflattengoogle-query-languagedata-transform

Transform a data table from column based data to list column headers by their unique table entries

googlesheet new user here. Am trying to wrangle to some data in the most efficient way possible. I have done this transformation already via a formula of lookups and if statements but have to believe there is a better way with a query function or similar? any guidance would be greatly appreciated. The image shows the transformation required - would also be grateful if you could suggest how this question could be better worded. Thank you.

Transformation - from/to

Solution

  • use:

    =ARRAYFORMULA(REGEXREPLACE(QUERY(TRIM(SPLIT(FLATTEN(
 QUERY(QUERY(SPLIT(FLATTEN(A2:B13&"¤×"&A1:B1&","), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), "¤")), 
 "where Col2 is not null", ), ",$", ))

    enter image description here