如何转置&拆分多列并在列中重复特定的单元格 [英] How to transpose & split multiple columns and repeat specific cells in a column

问题描述

我希望转置，拆分并保留正确的相应类别/参考编号.

• A列:类别/参考号.
• B列:电子邮件(CSV)

  |A |B ||A |B || 001 |电子邮件1，电子邮件2，电子邮件3 || 001 |电子邮件1 || 002 |电子邮件4，电子邮件5，电子邮件6 || 001 |电子邮件2 ||||| 001 |电子邮件3 ||||| 002 |电子邮件4 ||||| 002 |电子邮件5 ||||| 002 |电子邮件6 | 

I am looking to transpose, split, and keep the correct corresponding Category/Reference Number.

• Column A: Category / Reference Number.
• Column B: Email (CSV)

| A |        B           |    | A |  B   |
|001|Email1,Email2,Email3|    |001|Email1|
|002|Email4,Email5,Email6|    |001|Email2|
|   |                    |    |001|Email3|
|   |                    |    |002|Email4|
|   |                    |    |002|Email5|
|   |                    |    |002|Email6|

Here is another post which is similar to what I am looking to accomplish. The only difference is in this post, the OP requested that the formula duplicates data X times. Here is the formula that is used:

=ARRAYFORMULA({TRANSPOSE(SPLIT(CONCATENATE(REPT(B2:B&",", A2:A^2)), ",")), 
           TRANSPOSE(SPLIT(CONCATENATE(REPT(C2:C&",", A2:A)),   ","))})

I have tried modifying this formula by removing the "^2", "A2:A" replacing with a COUNTIF (to determine the number of emails in each row), and keep breaking the formula.

What am I doing wrong?

Here is my sheet.

解决方案

try:

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT('Form Responses'!C2:C, ","))="",,
 'Form Responses'!B2:B&"×"&SPLIT('Form Responses'!C2:C, ","))), "×"), 
 "where Col2 is not null")))

这篇关于如何转置&拆分多列并在列中重复特定的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！