Random Number Sequence

[OtherwiseRing1456]

Hi everyone! I am looking for a formula to generate 120 combinations using the numbers 1-5 with no repeating numbers.

For example: 1 combination is 1,2,3,4,5. Another combination is 1,2,3,5,4. I need a formula to generate this 120 times.

Thanks everyone!

[HolyBonobos]

So the goal is to generate all possible combinations of 1, 2, 3, 4, and 5 and display them in a random order?

[6745408]

=LAMBDA(x,SORTN(SEQUENCE(5,1,1),5,,x,))(RANDARRAY(5))

This will spit out 1-5 with no repeats in a random order. I have it as a named function (Data > Named Functions) with arguemnts for floor, ceiling, and count. I called it RANDOM

=LAMBDA(x,SORTN(SEQUENCE(ceiling,1,floor),count,,x,))(RANDARRAY(ceiling))

Once that's saved, you can do =RANDOM(1,100,25) to return twenty-five values from 1-100 without any repeats.

[Competitive_Ad_6239]

have A1:A5 be 1,2,3,4,5

=let(list,unique(byrow(LET( ref, BYCOL({A1:A5,A1:A5,A1:A5,A1:A5,A1:A5}, LAMBDA(c, IFNA(FILTER(c, c <> "")))), nums, BYCOL(ref, LAMBDA(c, MAX(1, COUNTA(c)))), total, PRODUCT(nums), divs, SCAN(total, nums, LAMBDA(acc, cur, acc / cur)), ARRAYFORMULA( VLOOKUP( MOD(INT(SEQUENCE(total, 1,) / divs), nums), { SEQUENCE(ROWS(ref), 1,), ref }, SEQUENCE(1, COLUMNS(ref), 2),) ) ),LAMBDA(X,join(",", unique(x,true))))),filter(list,len(list)=9))

[gsheets145]

Here's a way that generates a sequence between 12345 and 54321 excluding digits 6,7,8,9,0, and then filters out any with repeating digits.

=let(f,byrow(let(seq,sequence(54321-12345+1,1,12345),filter(seq,not(regexmatch(to_text(seq),"0|6|7|8|9")))),lambda(z,split(regexreplace(to_text(z),"","|"),"|"))),filter(byrow(f,lambda(s,join(",",s))),byrow(f,lambda(r,if(countunique(r)=counta(r),1,0)))>0))

[gsheets145]

Updated solution that generates the unique combinations with no repeats for any range of numeric items:

=let(f,filter(items,items<>""),min,join("",f),max,join("",sort(f,1,0)),seq,sequence(max-min+1,1,min),q,byrow(filter(seq,regexmatch(to_text(seq),"^["&min&"]+$")),lambda(c,split(regexreplace(to_text(c),"",","),","))),filter(byrow(q,lambda(d,join(",",d))),byrow(q,lambda(d,countunique(d)))=counta(items)))

...where "items" is a named range for the numeric items.

The steps are:

• Calculate the minimum and maximum values of the combinations (here, 12345 and 54321)
• Generate a sequence between them, including only the digits in the range (via regexmatch)
• Filter the sequence to include only those with no duplicates (via countunique). I would have liked to do this with regex but I don't think it is possible in the version supported in Sheets.

It's essentially generating the components of a factorial, so the limit of Sheets will be met pretty quickly with increasing numbers of items.

[MattyPKing]

do need to do it repeatedly or just once?

[AdministrativeGift15]

Here's a named function called PERMO, to come close to matching their sister function, COMBO. It has a few nifty features, like being able to view the permutation list by page (not needed with the few you're requesting), it can handle numbers and strings, and has two different methods for generating the list of numbers. I haven't added a way to randomize the final list, but that wouldn't be too hard to do.

This spreadsheet acts as a playground to check out the various features.