Search code examples
google-sheetstransposearray-formulasgoogle-sheets-querytextjoin

How could I form a string of items grouped by a matching value? (e.g. "Red: Apples, Cherries")

I'm wondering if there is a decent way to do this (without scripts) - if not, I can attempt creating a script for it but some users of this sheet will be using Excel on their computers so I'm trying to keep it scriptless as much as possible.

I have a sheet set up to display text based on certain conditions that is meant to be copied and pasted into an external program.

There is a column for months jan-dec and a column next to that where the user can input a number from 1-10 (and those numbers are associated with strings that are found with Vlookup on another sheet. They're basically "error codes" just to keep the sheet clean. But I'm just omitting this part because it's not needed for this question)

Right now, the text that populates shows:

Jan: 1
Feb: 2
Apr: 1

How could I group these by the value instead of listing them separately? Something like:

1: Jan, Apr
2: Feb

Is it possible to grab the items from that months list and put them in their own lists?

This is the current formula for reference:

=if(countif(Calculator!B2:B13,">0"),CONCATENATE(C2:C13),"None")

(Calculator sheet)B2:B13 --> column with the numbers

(Data sheet)C2:C13 --> a concatenated string that contains the month name from one cell and the number (or technically the string associated with that number as I mentioned before)

Each cell in the C column has the Jan: 1, Feb: 2 data and any month without data is left blank. When I concatenate the C cells together, it automatically omits the blank cells which is helpful but now I'd really like to group them by that value instead.

Here is the example sheet that reflects this

Solution

  • delete A15 and paste this in A14:

    ={""; ARRAYFORMULA(TEXTJOIN(CHAR(10), 1, REGEXREPLACE(TRIM(
 TRANSPOSE(QUERY(QUERY({A2:A13&",", B2:B13&":"}, 
 "select max(Col1) 
  where not Col2 matches ':' 
  group by Col1
  pivot Col2"),,9^9))), ",$", )))}

    0

    UPDATE:

    if order matters...

    ={""; ARRAYFORMULA(TEXTJOIN(CHAR(10), 1, REGEXREPLACE(TRIM(
 TRANSPOSE(QUERY(QUERY({"♦"&ROW(A2:A13)&"♦"&A2:A13&",", B2:B13&":"}, 
 "select max(Col1) 
  where not Col2 matches ':' 
  group by Col1
  pivot Col2"),,9^9))), "♦\d+♦|,$", )))}

    0

    UPDATE:

    ={""; ARRAYFORMULA(JOIN(CHAR(10), SUBSTITUTE(REGEXREPLACE(TRIM(QUERY(QUERY({
 SORT(FILTER({SUBSTITUTE(A1:A12, "'", "/"&20)*1, B1:B12&":"}, B1:B12<>""), 2, 1, 1, 1)}, 
 "select max(Col1)
  group by Col1
  pivot Col2
  format max(Col1) 'Mmm♦yy,'"),,99^99)), ",$", ), "♦", CHAR(39))))}

    0