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.
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 |