很像中数据转置的问题转置列数据 https://stackoverflow.com/questions/68426024/google-sheets-transpose-column-data-in-groups-into-rows我一直在尝试转置一组具有多个变量的数据。我面临的最大问题是尝试删除无用的数据。表1 数据接收方式
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 |
表2是所需的输出
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 |
|
|
我无法修剪细胞Alert and 出了些问题由于嵌套错误。
REDUCE
数组到字符串,由分隔符连接。如果值为日期,则加入????
,否则如果它是由下式确定的利息值REGEXMATCH
, 加入????
。从创建的字符串中,按行分隔符分割????
, TRANSPOSE
and SPLIT
通过列分隔符????
=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 |
|
|
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)