How to create a formula that will repeat a range by splitting one column and joining with the other column?
I have 2 columns, first name and surname. I want to split a cell that contains multiple values and then combine it with the cell on the right. I have no idea how to do this using a formula, please help.
Before:
| First Name | Surname |
|---|---|
| John,Jane,Mary | Fish |
| Albert,Steven,Alice | Smith |
Expected Result:
| First Name | Surname |
|---|---|
| John | Fish |
| Jane | Fish |
| Mary | Fish |
| Albert | Smith |
| Steven | Smith |
| Alice | Smith |
Solution
use:
=INDEX(QUERY(SPLIT(FLATTEN(IF(IFERROR(
SPLIT(A1:A, ","))="",,SPLIT(A1:A, ",")&""&B1:B)), ""),
"where Col2 is not null", ))
- How to create a formula that will repeat a range by splitting one column and joining with the other column?
- Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries
- How to transpose & split multiple columns and repeat specific cells in a column
- How do you deal with multiple values in a single cell separated by comma?
- Copy specific fields to another sheet
- Find Text and copy another cells
- Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
- How to use a query within a query?
- Find first empty row and scroll to it, Lastrow not working due to formulas
- Map a table, replacing values based on other cell
- Sum value if matching value is not blank in range leaving it blank if empty
- A Script to Simplify Creating a SO Table
- Google Sheets API v4 and valueInputOption
- Get Google Sheet by ID?
- ReferenceError: C is not defined
- How to sum up multiple VLOOKUP results?
- Conditional Formatting in Google Sheets with another table INDIRECT
- How to reference a cell to the left of active cell in Google script
- How to show instruction with image after install google workspace extension?
- How to use SpreadsheetApp.Spreadsheet.getChanges method?
- Get the user that changed specific cell
- Daytime and Nightime Occurrence Duration of an Event
- Recombining 4 pairs of numbers
- GmailApp.sendEmail: is it possible to schedule send an email?
- Why do I get the error SPLIT parameter 1 empty error when it isn't?
- Is there a faster way to sort sheets with AppScripts?
- ISO-8601 String to Date in Google Sheets cell
- How can we sort multiple values horizontally using VLOOKUP?
- COUNTIFS WITHIN DATE RANGE GOOGLE SHEET
- conditional formatting on a single cell where criteria is another column [solved]