Getting RANDBETWEEN to generate all numbers between x and y

[Excel_VBA]

Let's say I am using RANDBETWEEN (1,245). When I use this, although random numbers are generated, there are some numbers that do not appear between 1 and 245.

Is it possible to randomly generate numbers between x and y, but make all numbers between X and Y appear in any order?

 

[BenMiller]

How about creating a column with the values of 1 through 245, then a helper column with =RAND() dragged down, and sort on the second column? Sorting makes the volatile function RAND recalculate, so you'll get a different order each time.

 

[Aladin Akyurek]

Let's say I am using RANDBETWEEN (1,245). When I use this, although random numbers are generated, there are some numbers that do not appear between 1 and 245.

Is it possible to randomly generate numbers between x and y, but make all numbers between X and Y appear in any order?

A1: 245

A2, just enter and copy down:

=IF(ROWS($A$2:A2)<=$A$1,RAND(),"")

B2, just enter and copy down:

=IF($A2="","",RANK($A2,$A$2:INDEX(A:A,$A$1+ROW($A$1))))

B1, control+shift+enter, not just enter:

="All distinct? "&IF(SUM(IF(FREQUENCY($A$2:INDEX(A:A,$A$1+ROW($A$1)),
  $A$2:INDEX(A:A,$A$1+ROW($A$1))),1))=$A$1,"Yes","No")

for a fancy diagnostic.

 

[Excel_VBA]

How about creating a column with the values of 1 through 245, then a helper column with =RAND() dragged down, and sort on the second column? Sorting makes the volatile function RAND recalculate, so you'll get a different order each time.

Thank you to both of you. It works!

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!