Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries
I want to change the data from table on the left to table on the right by using formula or function. I already try some formulas but it only work for one column.
All help is welcome! Thank you.
Solution
Here's one approach you may test out:
=iferror(reduce(A1:C1,A2:index(A:A,match(,0/(A:A<>""))),lambda(a,c,{a;let(Σ,tocol(split(offset(c,,1),";")),choosecols({chooserows({c,offset(c,,2)},sequence(counta(Σ),1,1,0)),Σ},1,3,2))})))
- 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]