google-sheetssplitconcatenationtransposegoogle-query-language# 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.

Solution

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

- Google Sheets - Assign category based on list of search words
- Make non numerical content from a cell show up on another table according to certain criteria
- How to have this formatted data return empty given this is VLOOKUP + TEXT + QUERY in Google Sheets?
- Prevent Google sheet NOW() function from updating when now is set based on another cell's input
- Find which users are working on the same JobID
- I can't use FILTER with ARRAYFORMULA in Google Spreadsheet
- How to use a filter inside an array formula?
- Using an Arrayformula to sum another sheets values with a filter
- How to use ARRAYFORMULA + FILTER within Google Spreadsheet?
- How to select Mode of data that is text in google sheets with criteria?
- Is it possible to display an iframe or webview in google sheets
- SUMPRODUCT with Less than or equal to in ARRAYFORMULA
- Why my google sheets custom function doesn't work with arrayformula?
- MAXIFS in conditional formatting affecting values outside of set criteria?
- Best way to count or lookup if any cell in one row matches any cell in other row AND return value from top of row
- Is there any way to use apps script to add an exception to a sheet protection?
- How to use MINIFS in conditional formatting referencing another sheet?
- Google Apps Script not reading a value needing to be copied elsewhere
- Google Sheet - need time stamp for the cell row if certain columns are updated
- Sum column until first empty cell
- How do I combine the output of multiple QUERY functions?
- Add convert xls to Google sheets feature to function of download
- how can I set a formula in a box depending a word with conditional formating?
- cannot select active spreadsheet
- How to make conditional formatting of different type of dynamic array using formula in Google Sheets
- Update IMPORTRANGE formula on column change in the source sheet
- Skip a row when printing if certain columns are empty in google sheets
- How to send emails with google sheets using a template
- How to include an IF condition to stop sending duplicate emails in google apps script
- Get the last non-empty cell in a column in Google Sheets