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.
- Python RegExp global flag
- Validate Bangladeshi phone number with optional +88 or 01 preceeding 11 digits
- Regex to validate password strength
- Detect and parse a time range in a format like "2-4pm" for Amazon Lex scheduling bot
- What is the meaning of the Regular Expression ^(.)\1+$
- How to find and replace with regex in excel
- Issue with Regex Pattern (split string with characters & numbers)
- How to get the first Tamil letter in a word?
- The method of using Regular Expression to express Date and Time: YYYY-MM-DD HH:MM:SS.XXX
- validate the credit card expiry date using java?
- Using disjunction (OR) in a lookbehind
- See if a string contains any characters in it
- Multibyte trim in PHP?
- match only the last instance of a pattern with Javascript regexp
- Regex to detect negative numbers but not hyphenated numbers
- How to Fix regular expression capturing group error Bigquery?
- Improving the below RegEx for US and UK Names
- R quanteda kwic not matching negative look behind pattern
- Help with drivers license number validation regex
- Regex for matching grey colors in hexadecimal notation
- Extract email address from string
- Regex for ISO 8601 durations
- How to find second match in sql
- Regular expression to remove HTML tags
- How to replace host part of a URL using javascript regex
- How do I match all four cases in Regex?
- How to replace a period that is between letters, but not numbers?
- How to strip quotes from a variable in Ansible?
- Regular expression to accept negative number
- Negative Lookahead not working in perl regex