View Full Version : Nasty Excel worksheet - need ten numbers in a random order

09-02-2004, 08:56 PM
I need to do a grid of numbers for maths exercise for my son. Ten lines, ten rows. Each entry is randomly selected number from 1-10, but there shalt be no repeats.

How can I do this? I put this into the second column, to check if it repeats the first column. The chance of a repeat is not actually reduced, as the formula requires the recalced result regardles of the result so actually a complete bollocks.
=IF(INT(RAND()*10+1)=B1,INT(RAND()*10+1),INT(RAND( )*10+1))

I need to pick from a list somehow, until the numbers are "used up".

Any ideas?
PS Have a killer maths spreadsheet for exercising children on all sorts if anyone wants it. It's nasty, been developed over the years, includes random calcs for additional, subtraction, multiplication, division, negative numbers, decimals. I did say nasty.

09-02-2004, 09:00 PM
I dont know any thing about waht can be done in XL

from a programing angle I would start with a list on the numbers 1 to 10 in cells then pick two cells at random and switch the contence of those cells. repeat as many random swaps as you like.

09-02-2004, 09:10 PM
There was a thread on related matters in this Forum on and about 27 jan 2003.

Maybe you might find something in this.

If not I might have look at it for you, although it seems even such arcane stuff can sometimes generate not-very-well-informed dissent (not coming from you of course).

09-02-2004, 09:14 PM
Somehow randomly swapping or picking pairs might work. Don't want to resort to macros.

I could have a table of combinations that include every number in the range and randomly select a line in the table for a vlookup but then I have to do the sodding table and that is what I wanted to avoid.

Any other ideas?

09-02-2004, 09:44 PM
What do you mean - "no repeats". Do you mean the cells in each row (or column) will contain each integer from 1 to 10 once and only once? Or do you mean that there may not be rows (or columns) with an identical sequence? Or both?

Whatever your reply, I'm not sure if I can help, but it could be an interesting exercise...

Tony Bacon

09-02-2004, 09:49 PM
My suggestion would be to resort to making your own custom function which would give a much greater control over the set of numbers. Yould would need to have 9 input variables so that you can test that your random number has not already used and use a do loop to crate random number each time.

09-02-2004, 10:36 PM
I don't know how you translate it into "Excelese", but something like this ought to work:

dim Array1(10) ' 10 flags on if the number is selected, off if not

For rows 1 to 10
Set all array1 off
For cells 1 to 10
Do until array1 all on
Pick a random number (n) between 1 and 10
If array1(n) on then
do until array(n) off
Pick another random n
Put n into current cell
Next cell
Next row

Or maybe not!

Tony Bacon

09-02-2004, 10:37 PM
All my lovely indenting disappeared! And I think I just said the same thing as me_ill.

Tony B

09-02-2004, 10:53 PM
Hi, copy both lots of code into a module then just select 10 vertical cells then run the CreateUniqueRandomNumbers macro then repeat the process for each column. Code harpooned from here (http://www.exceltip.com/show_tip/Custom_Functions/Return_random_numbers_using_VBA_in_Microsoft_Excel/531.html) with a small adjustment. :-)

Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
' creates an array with NumCount unique long random numbers in the range LLimit - ULimit (including)
Dim RandColl As Collection, i As Long, varTemp() As Long
UniqueRandomNumbers = False
If NumCount < 1 Then Exit Function
If LLimit > ULimit Then Exit Function
If NumCount > (ULimit - LLimit + 1) Then Exit Function
Set RandColl = New Collection
On Error Resume Next
i = CLng(Rnd * (ULimit - LLimit) + LLimit)
RandColl.Add i, CStr(i)
On Error GoTo 0
Loop Until RandColl.Count = NumCount
ReDim varTemp(1 To NumCount)
For i = 1 To NumCount
varTemp(i) = RandColl(i)
Next i
Set RandColl = Nothing
UniqueRandomNumbers = varTemp
Erase varTemp
End Function

Sub CreateUniqueRandomNumbers()
Dim varrRandomNumberList As Variant
varrRandomNumberList = UniqueRandomNumbers(10, 1, 10)
Selection.Value = Application.Transpose(varrRandomNumberList)
End Sub

09-02-2004, 11:06 PM
Had a bit of a think about this and just want to be clear about what you want.

As far as I can tell you want to randomly reorder the numbers 1 to 10 without repetition.

Suppose that you put the numbers 1 to 10 in cells B1 to B10.
In A1 put =rand() and fill down to A10.

Then select the range A1 to B10 and sort on A1 using your sort up or sort down button.

The numbers 1 to 10 in col B will be randomly reordered, and you can rep[eat this as many times as you like.

This doesn't involve any code.

Is this the sort of thing you want?

10-02-2004, 07:58 AM
Thanks for suggestions, but have it sussed. Not perfect but it will do.
Do a list of numbers 1-10. Then put a random number formula in the left columun (simple one =rand() and that's it). Then sort the table on the random numbers, that orders the ten digits randomly. I then dumped another set of ten in 1-10 order alongside the random set, and repeated to have two different random orders, and repeated again, etc. Now have a table of 20-odd randomly selected sets, giving 400 possible combinations on the tables so repeats are unlikely to be detected.

The formula in the headings now reads:
in column 8, and:
=VLOOKUP(1,$N$1:$AI$10,$M$3) in row 1. (M1 and M3 are where the random numbers are, all entries across must use the same random number, and all down must use the same, different, random number.

Doing the table of numbers - ie answering them by multiplying - is damn hard. I can do 60 random ones in 60 seconds when I am motoring but the grid of 100 took me over 3 minutes.

Again, thanks for efforts.

Russell D
10-02-2004, 08:50 AM
I still don't quite follow,

but you can use =RANK(a1,$a1:$a10) copied from B1:B10 on a list of 10 random numbers in A1:A10 to generate the numbers 1 to 10 in the same random order as A1:A10

10-02-2004, 09:00 AM
Just curious wouldn't just starting each column with one number down have the same effect- 12345678910 then 23456789101 etc...Each cell has a different number and no number is repeated.

10-02-2004, 10:56 AM
I thought about that - but I guess it depends on how truly random you want the numbers to be. If random means you can'r predict the next number by inspecting the previous sequence, then your solution is not random. But maybe for the purposes of the exercise, it would be sufficient.

Tony Bacon

Chris Randal
10-02-2004, 12:23 PM
Have you had a look at that excellent little book by Rob Clarke?

;\ :D

10-02-2004, 04:51 PM
I refer to page 17 all the time (it does say you don't have to remember, so I don't).

Am going to look at RANK function.