0

I would like to generate all combination from a list a a column A I would like to return in rows/cells all value for lenght tuple = 12

I got so error because the reesult is too large to be printed.

Then I try to find a solution print by batch (example 1-20.000 / 20.001-40.000 ...etc...etc)

the formula I used is:

=arrayformula( split( 
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
  tocol(A1:A36 & "→" & transpose(
A1:A36)))))))))))))))))))))), "→" ) )

the list I want o use is

Column A
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z
0
1
2
3
4
5
6
7
8
9

enter image description here

I've read theses 2 questions/responses, but it doesn't helped me so much with my problem

How to list all permutations without repetition?

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

I need to know can i print/show the generated values by batch for example or divided in multi spreadsheets.

Thanks with your help. Max

lajd
  • 105
  • 9
Moi sur internet
  • 1

1 Answers1

0

If I understand correctly, you are attempting to list all possible 12 character length combinations of lowercase letters and digits, and are having issues due to an error displaying the output. I believe your printing issue stems from the size of the output.

26 letters + 10 digits = 36 options to choose from.

The number possible combinations of 12 from 36 is

C(36, 12) = 1251677700, over 1 billion.

Combinations assume order does not matter. If you intend for the order to matter we can do a permutation instead:

P(36, 12) = 5.9955562e+17, over a quadrillion.

These numbers are enormous and are beyond the limit of Google Sheets, which has a cell limit of 10 million cells.

lajd
  • 105
  • 9
  • Hello, Yes, I would like to get all combinations with repetition (example: 'aaaaaaaaaaaa') I would like to know if it's possible to output the result into multi spreadsheet file? I tried to use array_constrain to limit number of row to show, but i've the same message "value too large..." – Moi sur internet Jun 12 '23 at 16:25
  • Oh I think I may have misunderstood your question. Do you mean to get the value in column A repeated 12 times? Eg. if A2 is a, get aaaaaaaaaaaa, A3 is b, bbbbbbbbbbbb, etc. If so, you can use the [REPT](https://support.google.com/docs/answer/3094134?hl=en) function =REPT(A2, 12) – lajd Jun 13 '23 at 19:25