英文:
Split second column of two column data set and append to bottom
问题
以下是要翻译的内容：
首先，我有两列数据，如上所示。第一列只是一个单独的EAN，但第二列可以有逗号或下划线分隔的ASIN值（例如，ASIN1_ASIN2或ASIN1，ASIN2）。
我想要拆分ASIN列中的每个ASIN，然后将其附加到数据集的底部，与其对应的EAN一起（同时替换原始行）。
因此，对于下面的第68行，附加到底部的数据将是：
738678181690 B00EUTKDQS
738678181690 B000KEIPC6
738678181690 B01IAEN1PI
这将替换原始的第68行，以便原始行不再存在。
我想第一步是使用SPLIT函数，但很难想象如何在整个数据集上执行此操作（尤其是考虑到ASIN列可以是一个ASIN、两个ASIN（由逗号或_分隔）、三个ASIN，甚至有时是四个ASIN）。
英文:
So I have two columns of data of the form above. The first column is just a single EAN but the second column can have comma OR underscore separated ASIN values (so e.g. ASIN1_ASIN2 or ASIN1,ASIN2).
I would like to split each ASIN in the ASIN column and then append it to the bottom of the data set alongside its corresponding EAN (and at same time replace the original row).
So for row 68 below the appended data to the bottom would be:
738678181690 B00EUTKDQS
738678181690 B000KEIPC6
738678181690 B01IAEN1PI
And this will replace the original row 68 so that that doesn't exist anymore.
I guess the first step is using SPLIT but struggling to think of how to do it for the entire data set (especially given that ASIN column can be one ASIN, two ASINs (separated by the comma or _, three ASINs, or sometimes even four).
答案1
得分: 2
使用A2:A中的EAN和B2:B中的ESIN，尝试：
=arrayformula(query(split(flatten(A2:A&"|"&trim(split(B2:B,"_,",1))),"|"),"where Col2 is not null",0))
编辑
为了确保colA是数字：
=arrayformula(query(split(flatten(n(A2:A)&"|"&trim(split(B2:B,"_,",1))),"|"),"where Col2 is not null",0))
英文:
With your EANs in A2:A & your ESINs in B2:B, try:
=arrayformula(query(split(flatten(A2:A&"|"&trim(split(B2:B,"_,",1))),"|"),"where Col2 is not null",0))
EDIT
To ensure colA is numeric:
=arrayformula(query(split(flatten(n(A2:A)&"|"&trim(split(B2:B,"_,",1))),"|"),"where Col2 is not null",0))
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论