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", ))
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets
- Failed to submit form responses from a spreadsheet
- Maps.newDirectionFinder().SetArrive & SetDepart (Google Maps API) in GSheets App Script aren't impacting returned driving times w/ tested scripts
- Check if range is empty and copy data from Range
- How to convert duration of MM:SS to seconds in Google Sheets?
- Conditional formatting comparing cell to cell values in a range
- showModalDialog in SpreadsheetApp Won't Close
- Google Sheets Create Template