Search code examples
google-sheets

Formula for generating random four-digit numbers google docs

Recently, I've had a challenge

I need to google tables to generate a string of 4 digits, where each digit is unique and the difference between neighboring digits is not 1 I have tried to use and modify this formula:

=ARRAYFORMULA(JOIN(""; UNIQUE(TRANSPOSE(MID(TEXT(RANDBETWEEN(1000; 9999); "0000"); ROW(INDIRECT("A1:A4"))); 1)))))

But the problem with this formula is that the digits are not unique and the difference between neighboring digits is not taken into account I also tried to modify this formula:

=ARRAYFORMULA(JOIN("", UNIQUE(MID(TEXT(RANDBETWEEN(1000, 9999), "0000"), ROW(INDIRECT("A1:A4"))), 1))))

It generates unique digits, but does not take into account the difference between adjacent digits and sometimes returns two-digit and three-digit numbers

An example of an appropriate numbers: 1682, 2468, 3146

Is it possible to make a formula suitable for my conditions?

Solution

  • Also try this one:

    =reduce("",sequence(4),lambda(a,c,
let(seq,sequence(10,1,0),
filter,filter(seq,not(isnumber(find(seq,a))),(seq<>right(a,1)+1),(seq<>right(a,1)-1),(c>1)+(seq>0)),
choices,rows(filter),
a&index(filter,randbetween(1,choices)))))

    EDIT

    Above formula does not allow number to start with 1.

    Please try the following:

    =reduce("",sequence(4),lambda(a,c,
let(seq,sequence(10,1,0),
filter,filter(seq,not(isnumber(find(seq,a))),(abs(seq-right(a,1))>1)+(c=1),(c>1)+(seq>0)),
choices,rows(filter),
a&index(filter,randbetween(1,choices)))))

    Here are some numbers generated with this second formula:

    enter image description here