splitgoogle-sheets-formulatrimflattengoogle-query-language
Group everything by Column A and have Column B be a comma-delimited list of values
I've got a Google Sheets worksheet with data like this:
| Product | Attribute |
|---|---|
| Product A | Cyan |
| Product B | Cyan |
| Product C | Cyan |
| Product A | Magenta |
| Product C | Magenta |
| Product B | Yellow |
| Product C | Yellow |
| Product A | Black |
| Product B | Black |
What I'd like to do group everything by Column A and have Column B be a comma-delimited list of values that share Column A in common, like so:
| Product | Attribute |
|---|---|
| Product A | Cyan,Magenta,Black |
| Product B | Cyan,Yellow,Black |
| Product C | Cyan,Magenta,Yellow,Black |
Query ? Arrayformula ? Both ?
Solution
use:
=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(
QUERY(QUERY({ROW(A1:A), A1:A&"×", B1:B&","},
"select max(Col3) where not Col2 starts with '×'
group by Col1 pivot Col2"),,9^9)), "×")), ",$", ))
- How to replace split() with preg_split() in my PHP code?
- How to Split Image Into Multiple Pieces in Python
- "The big SQL RegEx": How do I RegEx split an easy SQL query (SELECT ... INTO ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY)?
- Split string string on the nth semicolon in a string
- Split string in half by a delimiting substring
- Split string between digital and non-digital characters
- Split text containing sentences and whitelisted phrases into separate lines and prepend each line with a counter
- Split string representing a comparison condition into its three parts
- Parse filename without delimiting characters into segments
- Split string on mid-string numbers without losing numbers in the result
- yq wont recognize single quotation marks around the field when trying to split large YAML file into smaller ones
- Matlab split string multiple delimiters
- Splitting HTML file and saving chunks using LangChain
- Split on regex (more than a character, maybe variable width) and keep the separator like GNU awk
- Split a string after every second character
- Wrapping subpattern in square braces does not encapsulate the match as expected in regular expression
- Separating a Dataframe Column by Multiple Delimiters Into New Columns Using Pandas
- Split string on an optional space between a punctuation mark and a letter
- Efficient way in using lookarounds for regex split
- How to split a column into two columns to separate two values in a cell when some cells don't contain two values to split using dplyr in R
- Split string on a space before one of two whitelisted words
- Split string containing numbered list items before each number followed by a dot then a space
- Split by new lines which are followed by a number or a letter then a dot
- How to split a string into only two parts (and not discard other parts)
- How to extract convert date in written text to numeric
- Splitting string from the first occurrence of a character
- Python Split files into multiple smaller files
- How do I split a string on a delimiter in Bash?
- Split long gtsummary() table to n smaller tables
- Split text into smaller parts identified by needle