2

The Situation: I'm creating a dice notation "Clean Up" formula, so that similar dice rolls are combined. For example: "1D6+1D6" would become "2d6". To complicate things, negative rolls (like "-1D6") can't be summed into the final result (Meaning, "1D6-1D6" does not equal "0"). Why? Because 1D6 does not equal the same number for each roll. So, the first (additive) dice roll could be "6", and then the penalty die roll could be 1, for a total of (6-1)=5.

Added factor of complexity... This isn't just for D6. It's the whole RPG set. D4, D6, D8, D10, D12, D20, and D100.

So, I've gotten SO CLOSE!!! But, the formula... It's LONG... Like, I copied and pasted it into MS Word, and it was two full pages long... You'll see...

https://docs.google.com/spreadsheets/d/14C_n53wgiQL6-rrOl-9IfXPBTMIdnsZxaZ5vmeKXsMU/edit?usp=sharing

(Other than the last minute D10 issue) It works... But, I plan on dragging this formula down the line at least 500 times, so it'll KILL the sheet speed.

The final formula is broken down by dice type. (The final formula is just a TEXTJOIN of all of the individual dice formulas).

While I don't know the solution to shortening this down, I did realize in writing this that perhaps it's not necessary to define each dice. Maybe sorted by the array of unique strings that come after "D" and before the next "+"?!?!

player0
  • 124,011
  • 12
  • 67
  • 124
Rogue Scientist Allen
  • 109
  • 7
  • Your question can be greatly improved if you add a table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Sep 18 '22 at 21:35

2 Answers2

1

try:

=INDEX(REGEXREPLACE(TEXTJOIN("+", 1, FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(IFNA(TRANSPOSE({
 REGEXEXTRACT(SPLIT(C5, "+"), "^\d+")*1;  REGEXEXTRACT(SPLIT(C5, "+"), "D\d+"); 
 REGEXEXTRACT(SPLIT(C5, "+"), "^-\d+")*1; REGEXEXTRACT(SPLIT(C5, "+"), "D\d+"); 
 REGEXEXTRACT(SPLIT(C5, "+"), "D(\d+)")*1}), 0), 
 "select sum(Col1),Col2,'+',sum(Col3),Col4,Col5 group by Col2,Col4,Col5 order by Col5"), 
 "select Col1,Col2,Col3,Col4,Col5 offset 1", )),,9^9))), " |\+ 0 D\d+", ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
1
=JOIN("+",BYROW(QUERY(REDUCE({"",""},SEQUENCE(2),LAMBDA(a,c,{a;QUERY({ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(B1,"+")),"D"))}," select sum(Col1),Col2 where Col1"&IF(c=1,">","<")&"0 group by Col2 label sum(Col1) ''")})),"order by Col2"),LAMBDA(r,JOIN("D",r))))

Split by +, Split by D, Group by the suffix and sum the prefix, join them back by D, then by +.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • It worked in my test sheet, but not in the actual sheet. I realized that my actual sheet has random "2D6+2" (or some other integer). I previously used a formula to strip out all free integers (The "+2" is taken care of elsewhere). So, "2D6+2+-1D6+1D4+1D4+-1D4" would end up as "2D6+-1D6+2D4+-1D4". (The "+2" goes to a different part of the sheet for different calculations). Is there a simple conversion to your amazing formula for this? (And my apologies for not including that in the original post). – Rogue Scientist Allen Sep 20 '22 at 00:35
  • If it helps, this is the error it displays when an integer is involved. Error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. – Rogue Scientist Allen Sep 20 '22 at 00:39
  • 1
    @RogueScientistAllen I don't understand your desired output. Kindly ask a new question with a table for input, a table for expected output, and anything you've tried to achieve your goal. This question already has two answers. Changing the initial goal post after receiving a answer is frowned upon. – TheMaster Sep 20 '22 at 08:23
  • Thank you, and will do! I appreciate your help on this. I will make a more specific example giving the potential variables I'll be working with. – Rogue Scientist Allen Sep 20 '22 at 14:24