Spread single row into multiple in Google Sheets
I have data in a google sheet in the following format
| group_id | id_1 | id_2 | id_3 | id_4 |
|---|---|---|---|---|
| 1 | b2260c55-c4a2-4f16-840f-6e0a572861ee | e63c6878-1ea9-4715-ae11-82d09e788a2d | 2e381820-1b30-49d5-9e04-d5eff03a0341 | |
| 2 | 9a6dfdd4-204d-46b6-95bb-a1fb2323ea79 | 3f5d47c4-971a-4dfc-b05e-e664b96584e8 | ||
| 3 | 4e9e58b4-1eae-4a2c-bc7d-cfe90099d154 | e19c3aba-3c50-483a-9363-dfc538458bd4 | 93b8534b-902b-4404-8a10-0cbb9bd1db35 | 02ffa092-a305-4783-8c51-7cf71e7384d0 |
Each group_id has anywhere from 2-15 IDs listed next to it. What I want to do is take each row and split it into multiple rows based on the number of IDs. So taking just group_id 1 as an example the below is my desired output
| group_id | id |
|---|---|
| 1 | b2260c55-c4a2-4f16-840f-6e0a572861ee |
| 1 | e63c6878-1ea9-4715-ae11-82d09e788a2d |
| 1 | 2e381820-1b30-49d5-9e04-d5eff03a0341 |
Is there a way I can do this using formulas or other Gsheet tools other than scripting? I need to do this as a one-time thing so even a multi-step process works as long as I do not need to do this super manually. Thanks in advance!
Solution
Assuming the table is in A1:E, you can use the following formula:
=ARRAYFORMULA(QUERY(SPLIT(TOCOL(A2:A&"|"&B2:E),"|"),"where Col2<>''"))
- How to show "game over" when the Countdown is over in google sheets
- How do I add days to a timestamp script?
- A value error appears when I use a formula in a pivot table
- Error trying to get list of sheet names and ranges in query formula in Google Sheets
- QUERY + BYROW + LAMBDA display dates as raw numbers instead of the original values
- Transform string to variable
- Google Sheets Script new Date() with timezone is providing wrong time
- Count MAX Date Meeting Criteria in Cells Containing Multiple Dates
- Send data from one google sheet to a another google sheet with app script
- Is it possible to insert more than one image in a cell?
- AVERAGEIF a value appears in other columns of a row in Google Sheets
- Scraping Instagram data using google spreadsheet?
- Need help creating a formula
- Is there a way to create a Sheets Table from apps script?
- shortest formula to get the last non zero value from column
- How to Extract Both Name and Link from Google Sheets Smart Chip (Place) Using Apps Script?
- Summing certain rows within a Google sheets table
- Add Column After Importrange
- Replace Indirect in a Countifs formula in Google Sheet
- Odd QUERY result in Google sheets?
- Suppress #N/A returned by Google Sheets vlookup
- Google sheets: Create function that calculates depending on dropdown menu text
- Parse JSON import data string into two columns
- Transfer data with GOOGLE SHEETS
- Selecting the last value of a column
- Adjusting A Script That Deletes Duplicates On Other Sheets
- Adding a hyperlink to an inline image on Google Appscript
- Setting min max values for the Horizontal Axis on a Google Sheets chart
- How to match invisible new lines?
- convert two columns containing group and items in group into a single column