Multiply all possible combinations for rows

Question

Hi I have a similar problem in Google sheets that is solved in this thread:

Generate all possible combinations for Columns(cross join or Cartesian product)

What I'm looking for is instead of combining the columns, multiply one numbers in each row for 6-8 rows.
As (A1 OR B1 OR C1)(A2 OR B2 OR C2)(A3 OR B3 OR C3)(A4 OR B4 OR C4)(A5 OR B5 OR C5)*(A6 OR B6 OR C6)

E.g: A1B2C3, B1B2C3, A1A2A3 etc.

I'm a complete newbie to spreadsheets but you got to start somewhere right? I hope my post is readable although my poor english.

Thanks!

I've tried the formula:

=INDEX(FLATTEN(FLATTEN( FILTER(A2:A; A2:A<>"")&" "&TRANSPOSE( FILTER(B2:B; B2:B<>"")))&" "&TRANSPOSE( FILTER(C2:C; C2:C<>""))))

but when I do so, the results is shown as e.g. "1,13 5,86 0,58" instead of "3,84" ((1,13)(5,86)(0,58))

Here is a copy of my sheet where I try to explain it: https://docs.google.com/spreadsheets/d/19_ULULn63YYktWkkgBH0cdK9ZJnkk0PBBiBW1mNrgkU/edit#gid=0

please share a sample sheet with example of your data and desired result. You can [Make an anonymous sample document](https://docs.google.com/forms/d/e/1FAIpQLScf4e8rJpjbDx-SQOH2c2xIaUP-ewnNJoqv9uRAXIrenUvZ_Q/viewform) — z'', Nov 30 '22 at 08:32
share a copy / sample of your sheet with an example of the desired output — player0, Nov 30 '22 at 20:54
Thank you for your reply @ztiaa and player0 . https://docs.google.com/spreadsheets/d/19_ULULn63YYktWkkgBH0cdK9ZJnkk0PBBiBW1mNrgkU/edit#gid=0 Here is a copy of the sheet. I hope it's understandable! I've tried to explain it as good as possible. Just write if you wonder anything. I am very grateful for all the help I can get. — TheEmenike, Dec 04 '22 at 23:42

score 0 · Answer 1 · answered Nov 30 '22 at 05:19

0

You can adapt your formula to split each row and multiply:

=BYROW( YOUR_FORMULA,LAMBDA(each,PRODUCT(SPLIT(each," "))))

answered Nov 30 '22 at 05:19

Martín

7,849
2
3
13

score 0 · Answer 2 · answered May 03 '23 at 18:08

You can generate the Cartesian product and then multiply the combinations on a row-by-row basis with this recursive lambda function:

=let( 
  table, Sheet1!J2:L7, 
  blank, iferror(1/0), 
  first_, lambda(array, tocol(choosecols(array, 1), true)), 
  rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))), 
  wrap_, lambda(array, wrapCount, wraprows(tocol(array, true), wrapCount)), 

  cartesian_, lambda(a, b, wrap_( 
    byrow(a, lambda(row, 
      reduce(blank, sequence(rows(b)), lambda(acc, i, 
        { acc, row, chooserows(b, i) } 
      ) ) 
    ) ), 
    columns(a) + columns(b) 
  ) ), 

  iterate_, lambda( 
    self, a, b, if(iserror(b), a, 
      self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1)) 
    ) 
  ), 

  combos, iterate_(iterate_, first_(table), rest_(1)), 
  byrow(combos, lambda(row, product(row))) 
)

See the sample spreadsheet.

Multiply all possible combinations for rows

2 Answers2