Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

google sheets - How to transpose & split multiple columns and repeat specific cells in a column

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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")))

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...