Search code examples
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-query-language

Google Sheets - transpose irregular column data in groups into rows


Much like the problem with the transposing of data in transpose column data I am stuck trying to transpose a set of data with multiple variables. The biggest issue I face is trying to remove useless data. Table 1 is how the data is received

Column N
Sep 07 2022
Alert
Something went wrong
fish company
70000123456
1234567
231.03
View Details
Sep 07 2022
---
meat company
70000987654
688773
View Details
Sep 07 2022
Success
produce company
70000192837
View Details

Table 2 is the desired output

Column A Column B Column C Column D Column E
date vendor po Invoice cost
Sep 07 2022 fish company 70000123456 1234567 231.03
Sep 08 2022 meat company 70000987654 D688773B
Sep 07 2022 produce company 70000192837

I was unable to trim cells Alert and Something went wrong due to nesting errors.


Solution

  • REDUCE the array to the string, joined by delimiters. If the value is a date, join by 🍚, else if it's a value of interest determined by REGEXMATCH, join by 🐇. From the created string, split by the row delimiter 🍚, TRANSPOSE and SPLIT by the column delimiter 🐇

    =ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(REDUCE(,A2:A20,LAMBDA(a,c,IFS(ISDATE(c),a&"🍚"&TO_TEXT(c),REGEXMATCH(TO_TEXT(c),".*company|70{5}\d+|\d+"),a&"🐇"&c,TRUE,a))),"🍚")),"🐇"))
    
    Sep 07 2022 fish company 70000123456 1234567 231.03
    Sep 07 2022 meat company 70000987654 688773
    Sep 07 2022 produce company 70000192837