RANDOM NUMBERS

[dahowarduk]

I want to create all of the integers from 1 to 100, in a random sequence?
Any ideas?
Thanks

 

[alansidman]

Here is an array formula that works in O365. Since you have not provided your version (ie, 2010, 2013, etc) which is helpful in determining a solution I have proved one that will work in O365

=SORTBY(SEQUENCE(100,,1,1),RANDARRAY(100))

 

[p45cal]

1. In cell A1 and below enter the numbers 1 to 100 in sequence (you can do this quickly by typing1 in A1 and 2 in A2, then selecting both cells and using the grab handle in the bottom right drag down to A100).
In cell B1 type the formula
=RAND()
copy down to B100.
Select A1:B100 and sort on column B.

or

2. In any cell:
=SORTBY(SEQUENCE(100),RANDARRAY(100))

The trouble with suggestion 2 is that the random order will change every time the sheet recalculates.
The problem with suggestion 1 is that you're left with a column of random numbers from 0 to 1 and have to do a fresh sort for a new sequence.

In the attached is solution 2 (above) and…

3. Power Query solution which only updates when you refresh the query by right-clicking somewhere in the result table at cell E3 and choosing Refresh.

 

[Logit]

A VBA approach :

Option Explicit

Sub Ran1to100()
Dim anArray(1 To 100) As Long
Dim i As Long
Dim randIndex As Long, temp As Long
For i = 1 To 100
anArray(i) = i
Next i
For i = 1 To 100
Randomize
Do
randIndex = (Rnd() * 100) + 1
Loop Until randIndex <= 100
temp = anArray(randIndex)
anArray(randIndex) = anArray(i)
anArray(i) = temp
Next i
Range("A1:A100").Value = Application.Transpose(anArray)
End Sub

 

[dahowarduk]

Here is an array formula that works in O365. Since you have not provided your version (ie, 2010, 2013, etc) which is helpful in determining a solution I have proved one that will work in O365

=SORTBY(SEQUENCE(100,,1,1)

Here is an array formula that works in O365. Since you have not provided your version (ie, 2010, 2013, etc) which is helpful in determining a solution I have proved one that will work in O365

=SORTBY(SEQUENCE(100,,1,1),RANDARRAY(100))

Thanks

 

[dahowarduk]

Here is an array formula that works in O365. Since you have not provided your version (ie, 2010, 2013, etc) which is helpful in determining a solution I have proved one that will work in O365

=SORTBY(SEQUENCE(100,,1,1),RANDARRAY(100))

Thanks

 

[dahowarduk]

1. In cell A1 and below enter the numbers 1 to 100 in sequence (you can do this quickly by typing1 in A1 and 2 in A2, then selecting both cells and using the grab handle in the bottom right drag down to A100).
In cell B1 type the formula
=RAND()
copy down to B100.
Select A1:B100 and sort on column B.

or

2. In any cell:
=SORTBY(SEQUENCE(100),RANDARRAY(100))

The trouble with suggestion 2 is that the random order will change every time the sheet recalculates.
The problem with suggestion 1 is that you're left with a column of random numbers from 0 to 1 and have to do a fresh sort for a new sequence.

In the attached is solution 2 (above) and…

3. Power Query solution which only updates when you refresh the query by right-clicking somewhere in the result table at cell E3 and choosing RefresT

 

[dahowarduk]

Thanks

 

[dahowarduk]

Here is an array formula that works in O365. Since you have not provided your version (ie, 2010, 2013, etc) which is helpful in determining a solution I have proved one that will work in O365

=SORTBY(SEQUENCE(100,,1,1),RANDARRAY(100))

Thanks

 

[dahowarduk]

Thanks all