0

Is there a way to get a mathematical combination of all items in a row, and do math and such to generate things on another page based on said combination? e.g. {1, 2, 3} -> {1, 2, 3, 1 and 2, 1 and 3, 2 and 3, 1 and 2 and 3}

i need to sum the values of column b and c, separately, based on the rows corresponding to the items marked in the array of combinations.

screenshots for clarity. I need to use Sheet 1, which is all manual entry, to generate Sheet 2 in its entirety.

Sheet 1

Sheet 2

If need be it can be all in the same sheet instead of 2, but separate would be preferred for legibility

Edit: NB: I do realize this will probably take 3 or more formulas. Also, thanks in advance!

jeremiah
  • 49
  • 1
  • 6
  • 1
    Is there a function to spit out all different possibilities of ₃Cₓ ? If not, You might need Google-Apps-Script . Add that tag, if you need to use scripts? – TheMaster Sep 22 '17 at 07:00
  • 1
    I've no idea, but I've added the tag. thanks for the advice :) – jeremiah Sep 22 '17 at 07:16

1 Answers1

3

Question #1. Get all possible combinations

This formula will produce all possible combinations from strings in Sheet1 column A:A:

=TRANSPOSE(SPLIT(TEXTJOIN("",1,ArrayFormula(IF(REGEXEXTRACT(DEC2BIN(ROW(INDIRECT("a1:a"&SUM(FACT(COUNTA(Sheet1!A:A))/(FACT(ROW(INDIRECT("a1:a"&COUNTA(Sheet1!A:A))))*FACT(COUNTA(Sheet1!A:A)-ROW(INDIRECT("a1:a"&COUNTA(Sheet1!A:A)))))))),COUNTA(Sheet1!A:A))&"1",REPT("(.)",COUNTA(Sheet1!A:A)+1))*1,TRANSPOSE({FILTER(Sheet1!A:A,Sheet1!A:A<>"")&",";"#"}),""))),",#",0))

will produce:

Item3
Item2
Item2,Item3
Item1
Item1,Item3
Item1,Item2
Item1,Item2,Item3

If you add another item in sheet1, formula would adjust.

See math background here:

http://mymathforum.com/advanced-statistics/2567-non-repeating-combinations.html

Question #2. Sum by joined strings

In Sheet2 A:A we have joined strings. Paste this formula in B1 Sheet2:

=QUERY(QUERY({TRANSPOSE(SPLIT(JOIN("",ArrayFormula(REPT(row(OFFSET(A1,,,COUNTA(A:A)))&",",LEN(OFFSET(A1,,,COUNTA(A:A)))-LEN(SUBSTITUTE(OFFSET(A1,,,COUNTA(A:A)),",",""))+1))),",")),ArrayFormula(SUMIF(Sheet1!A:A,TRANSPOSE(SPLIT(TEXTJOIN(",",1,A:A),",")),Sheet1!B:B))},"select Col1, sum(Col2) group by Col1 label sum(Col2) ''"),"select Col2")

The result is:

Item3               1000
Item2               750
Item2,Item3         1750
Item1               500
Item1,Item3         1500
Item1,Item2         1250
Item1,Item2,Item3   2250
Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • I'm not quite at home yet to verify it max, but I did have a couple of questions: do I change that +1 in there to a +2 to get it to perform the same action on column c? also, as I'm a scrub at sheets functions, it looks like your using column a of sheet 2 to go back and find the values to sum for column b.. considering that, am I right in guessing that attempting to sort by column b, preserving rows, would not work? – jeremiah Sep 22 '17 at 12:12
  • oops forgot to catch you in this. @maxmakhrov – jeremiah Sep 22 '17 at 12:32
  • to make it work for column C replace `Sheet1!B:B` to `Sheet1!C:C`. – Max Makhrov Sep 22 '17 at 12:44
  • oh blimey, I was even looking for B references. now i feel quite silly. many thanks. i should imagine i can figure out how to use a third sheet to find a top 5 values and the corresponding rows (i'm a scrub but i can read and learn well enough) using the things you've posted. – jeremiah Sep 22 '17 at 13:05
  • looks like everything is working great, thanks much @maxmakhrov. – jeremiah Sep 22 '17 at 13:09
  • Never would I have thought I would see the day, When all possible combinations are spitted out with just formula! You're awesome!! @Max – TheMaster Sep 22 '17 at 14:05
  • The answer is indeed worth an upvote and awesome as an example of what you can just with with a formula. Having said that, as a software engineer it would be probably easier to write a function to generate the combinations from scratch in JS/App Script than understand how the formula actually works... – ttarchala Sep 22 '17 at 14:43
  • @ttarchala, Based on what I've seen in the formula so far (Though, I'm still wrapping my head around this), It creates a array of all possible combinations in 1s and 0s for the IF formula, then it transposes them over to the actual items. It's a highly complex form of `IF({1,0},,)` – TheMaster Sep 22 '17 at 15:53
  • @ttarchala, I agree. that'd be easier, and I know enough to do that with c#, but this is far more legible an output. that's why I went for sheets and asked for help :p – jeremiah Sep 22 '17 at 16:28