google-sheetsgoogle-sheets-formulaarray-formulas
Google sheets - join one value to each value in comma separated list and generate a single list of the results
I have a list like the following in Google Sheets (2 columns)
_A_ _B_________________________________
_1_ 932 [email protected],[email protected]
_2_ 343 [email protected],[email protected],[email protected]
_3_ 198
_4_ 197 [email protected]
_5_ 231 [email protected],[email protected]
I want to generate a single list like this...
_A_ _B_________
_1_ 932 [email protected]
_2_ 932 [email protected]
_3_ 343 [email protected]
_4_ 343 [email protected]
_5_ 343 [email protected]
_6_ 197 [email protected]
_7_ 231 [email protected]
_8_ 231 [email protected]
So far, I've managed to make this in C1...
=arrayformula(IF(B2:B5="","",concat(A2:A5,CONCAT("|",split(B2:B5,",")))))
...which generates this...
_A_ _B_________________________________ _C_____________ _D_____________ _E_____________
_1_ 932 [email protected],[email protected] 932|[email protected] 932|[email protected] 932|
_2_ 343 [email protected],[email protected],[email protected] 343|[email protected] 343|[email protected] 343|[email protected]
_3_ 198 198| 198| 198|
_4_ 197 [email protected] 197|[email protected] 197| 197|
_5_ 231 [email protected],[email protected] 231|[email protected] 231|[email protected] 231|
... and now I am very stuck. Please help!
Solution
Please use the following formula
=QUERY(arrayformula(IFERROR(SPLIT(flatten(IF(B2:B="","",concat(A2:A,CONCAT("|",split(B2:B,","))))),"|"))),"where Col2 is not null")
How the added functions work
The key function here is the (undocumented) flatten function.
When we use flatten on an array of cells, it transposes the array row by row into a single column.
Please notice the difference between flatten and TRANSPOSE
+------+-----+----+-----------------+----------+----------+
| array of cells | =flatten(A1:C2) | =TRANSPOSE(A1:C2) |
+------+-----+----+-----------------+----------+----------+
| 1 | 2 | 3 | 1 | 1 | 4 |
| 4 | 5 | 6 | 2 | 2 | 5 |
| | | | 3 | 3 | 6 |
| | | | 4 | | |
| | | | 5 | | |
| | | | 6 | | |
+------+-----+----+-----------------+----------+----------+
Once we get everything in one column we use SPLIT once again and finally the QUERY function to get rid of the rows without a value in the second column.
The IFERROR function is not actually needed it here but we usually apply it as a precautionary measure.
Functions used:
- How can I remove consecutive duplicates from a range in Google Sheets?
- Using FILTER with multiple conditions on Google Sheet
- How to union ranges in google spreadsheets
- Formula for filtering list based off of key with multiple criteria in Google Sheets
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria
- How to use Apps script to move a lot of data rows at same time to different spreadsheet automatically base on time
- How to ignore empty rows when submitting to database
- Custom Menus Google Apps Script
- Google Sheets Index Sidebar of all tabs - add search functionality
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Google script Display array with rows of different sizes
- How to Delete Today's Google Calendar Entries in Google Apps Script
- List rows in array when the cell contain number in the string
- ImportXML XPath issue using Google Sheets on a simple web scraping query
- Return index of cell that contains match on two strings
- Conditional formatting if cell past date and another cell empty with arrayformula
- How to find the Second Space from the right in Google Sheets?
- Joining more than two WHERE statements in Query Language
- Python gspread create sheet if not exist
- Find if cell contents exist in array
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- Active VS Effective User for Google Sheets
- Rearrange data in table format to rows
- I am not able to extract URL from these cell values using Google Apps Script
- How to count a specific word from a string in a cell from the entire column in google sheets
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script