regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
JOIN results of VLOOKUP into one string in Google Sheets
I have following task for my Google spreadsheet: JOIN strings in all cells that are to the right of certain id.
- To phrase it differently:
SELECT A, JOIN(',', B) GROUP BY A, WHERE A = myid;if JOIN was an aggregation function. - Or in other words:
=JOIN(',', VLOOKUP(A:B, myid, 0))if VLOOKUP could return all occurences, not just first one. - One picture better than of 1000 words:
Is this possible with Google Spreadsheets?
Solution
I believe you could use the FILTER function instead of VLOOKUP to filter a range based on an ID and then JOIN the returned range.
The documentation for FILTER is here: https://support.google.com/docs/answer/3093197
For example:
- You put
=UNIQUE($A:$A)in D2 to get all the IDs. - Then in E2 you put
=IF($D2="", "", JOIN(",", FILTER($B:$B, $A:$A=$D2)))and then copy it down.
- Eliminate meaningless words from a block of text
- JavaScript Split String with regex - match (how to replace match regex into split function)
- "Begins with" in Twig template
- Regular Expression Tester for Google RE2
- Perl's capture group disappears while in scope
- RegEx to find string representing local paths
- Character limit in a regular expression Javascript
- How do I create a regex dynamically using strings in a list for use in a pandas dataframe search?
- Remove non-utf8 characters from string
- Non greedy (reluctant) regex matching in sed?
- Regex to substitute the next two words after a matching point
- Expressing basic Access query criteria as regular expressions
- Regex to match email addresses, and common obfuscations
- Regular expression isn't matching all hyperlinks in a string
- How to scrape iframe content using cURL
- How to use a regular expression in a jQuery selector?
- How to getElementByName() with a regex?
- Regular expressions with O(N) and backreferences support
- Split a string by commas which are not inside potentially nested parentheses
- Split a string on commas which are followed by a space, word, then a colon
- How to not match (negative match) certain patterns in lookbehind
- Notepad++ and regex: how to UPPERCASE specific part of a string / find / replace
- Remove duplicated words from a space delimited string
- How to understand the star quantifier (*) output
- shell script: search and replace over multiple lines
- Regex pattern to replace any occurrence of {...} with actual values
- Split string on dots unless the dot is inside double quotes
- Get all alphanumeric words with length of either 4 or 12 characters
- Replace the trailing digits of a URI with corresponding slug text
- Wrap <b> tags around substrings matching a regular expression