2

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

player0
  • 124,011
  • 12
  • 67
  • 124
Ali
  • 173
  • 1
  • 3
  • 13

1 Answers1

2

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

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you! Definitely wouldn't have come up with that myself haha That's perfect. – Ali Feb 14 '20 at 20:53
  • 1
    It doesn't matter but this is still helpful information, thanks! – Ali Feb 14 '20 at 21:07
  • the months appear in random order (1: dec, apr, jan) any advice on how to have the months list in order rather than the number list item? – Ali Mar 10 '20 at 17:19
  • yeah I did, I think that order just goes based on the 1,2,3 rather than the months. but I'm going to mess around with it. Sorry for the delayed response – Ali Mar 17 '20 at 18:32
  • https://docs.google.com/spreadsheets/d/1oIC7ESrzAuirZJ4WkbcJZrUVm4S9-HKRs_RagTyFCfI/edit?usp=sharing – Ali Mar 31 '20 at 17:43
  • Basically, the first group seems to be in order but not the second row. I'm not sure, I could have done something wrong. – Ali Mar 31 '20 at 17:43