google-sheetsjoinsplitflattentextjoin
Formula to create a row for every value of a comma-delimited list - GSheets
I am trying to write a formula that will take a set of columns and pair them into separate rows with a comma-delimited list. In SQL, I would do this with a left join, but I am not sure how to leverage GSheets functions for this.
Here is what I mean:
Source Data
| Col1 | Col2 | CommaDelim |
|---|---|---|
| Col1Val1 | Col2Val1 | 1,2,3 |
| Col1Val2 | Col2Val2 | 1 |
| Col1Val3 | Col2Val3 | 1,2 |
| Col1Val4 | Col2Val4 | 1,2,3,4 |
Desired Output
| Col1 | Col2 | CommaDelim |
|---|---|---|
| Col1Val1 | Col2Val1 | 1 |
| Col1Val1 | Col2Val1 | 2 |
| Col1Val1 | Col2Val1 | 3 |
| Col1Val2 | Col2Val2 | 1 |
| Col1Val3 | Col2Val3 | 1 |
| Col1Val3 | Col2Val3 | 2 |
| Col1Val4 | Col2Val4 | 1 |
| Col1Val4 | Col2Val4 | 2 |
| Col1Val4 | Col2Val4 | 3 |
| Col1Val4 | Col2Val4 | 4 |
Solution
try:
=INDEX(QUERY(SPLIT(FLATTEN(A1:A&"×"&B1:B&"×"&SPLIT(C1:C, ",")), "×"),
"where Col3 is not null"))
- How to Extend a Formula to Apply to All Rows Using MAP or ARRAYFORMULA?
- Google Sheet - copy range if match
- shortest formula to get the last non zero value from column
- Copying data from a sheet to another with conditions
- How can I automatically generate due dates in Google Sheets based on payment plans?
- If one cell IS NOT EMPTY put text on another cell in Google Sheets
- How can I create a simple duplicate detector?
- Authorization is revoked every week. How to maintain persistent authorization?
- Conditional borders in Google Sheets
- Automatically adding a row to a sheet with data from another sheet once every day
- Adding borders based on grouped rows and conditional formatting in Google Sheets
- Illegal Argument Error Thrown When Trying to setTextStyle() for Rich Text in Google Apps Script
- Passing variables to time based triggers
- Google Apps script to conditional format cell border
- Multiple dropdown questions on a google-form tied to spreadsheet
- Google Apps Script sort stopped working "Exception: This operation is not supported on a table header row."
- count number of cells with precise number of repeated characters
- How can I extract columns based on multiple column value/criteria?
- How to count distinct column values in Google Sheets?
- Pivot table (calculated field)
- How do I get Google Script to read the display value of a sheet's cell? It keeps returning some random date instead
- Google Sheets - make a list of the names in column A where column E is greater than zero
- Allocating Specific Leaves and getting Calculation
- Randomize cells in Google Sheets
- Google sheet insertImage function stopped working for Google Apps Script: verify image error
- Calculation limit - MAP, LAMBDA, REGEXMATCH
- Automating Date Headers for printable monthly attendance sheets
- Is it possible to browse a spreadsheet when an importrange has been authorized?
- Disable Spreadsheet copy - Google Sheets
- Gsheet AppScript ShowModalDialogue no longer displaying HTML hyperlink