Random number generation without repeating across same row or column?

[Simtech76]

Hello,

Been trying to think of a way to get random numbers 1 to 36 generated without repeating in the same column, however also getting it to perform the same opertaion in 9 more columns (B:J) without the same number appearing in the same row.

example

1 2 5 4
2 5 3 1
3 4 1 5
4 1 2 3
5 3 4 2

Unsure if this is possible, have created a basic script for random number generation but have no clue how to expand across columns.

Thanks, Simon

 

[CARBOB]

Your efforts will be greatly appreciated, no hurry, whenever you get to it.

 

[mirabeau]

Just for fun, you might be interested in trying this code on a blank worksheet.

Sub just4fun()
ActiveSheet.UsedRange.ClearContents
Dim n As Long, j As Long, x As Long, y As Long
Dim a(), u()
n = InputBox("How many?")
ReDim a(1 To n, 1 To 2)
For i = 1 To n
    a(i, 1) = i
    a(i, 2) = i
Next i
For j = 1 To 2: For i = 1 To n
    x = Int(Rnd * (n - i + 1)) + i
    y = a(x, j)
    a(x, j) = a(i, j)
    a(i, j) = y
Next i, j

ReDim u(1 To n, 1 To n)
For j = 1 To n
    For i = 1 To n
        y = a(i, 1) + a(j, 1) - 1
    If y > n Then y = y - n
    u(a(i, 2), j) = y
    Next i
Next j
Cells(1).Resize(n, n) = u
End Sub

 

[CARBOB]

Just for fun, you might be interested in trying this code on a blank worksheet.

Sub just4fun()
ActiveSheet.UsedRange.ClearContents
Dim n As Long, j As Long, x As Long, y As Long
Dim a(), u()
n = InputBox("How many?")
ReDim a(1 To n, 1 To 2)
For i = 1 To n
    a(i, 1) = i
    a(i, 2) = i
Next i
For j = 1 To 2: For i = 1 To n
    x = Int(Rnd * (n - i + 1)) + i
    y = a(x, j)
    a(x, j) = a(i, j)
    a(i, j) = y
Next i, j

ReDim u(1 To n, 1 To n)
For j = 1 To n
    For i = 1 To n
        y = a(i, 1) + a(j, 1) - 1
    If y > n Then y = y - n
    u(a(i, 2), j) = y
    Next i
Next j
Cells(1).Resize(n, n) = u
End Sub

The members of this forum never cease to amaze me, thank you.

 

[CARBOB]

Just for fun, you might be interested in trying this code on a blank worksheet.

Sub just4fun()
ActiveSheet.UsedRange.ClearContents
Dim n As Long, j As Long, x As Long, y As Long
Dim a(), u()
n = InputBox("How many?")
ReDim a(1 To n, 1 To 2)
For i = 1 To n
    a(i, 1) = i
    a(i, 2) = i
Next i
For j = 1 To 2: For i = 1 To n
    x = Int(Rnd * (n - i + 1)) + i
    y = a(x, j)
    a(x, j) = a(i, j)
    a(i, j) = y
Next i, j

ReDim u(1 To n, 1 To n)
For j = 1 To n
    For i = 1 To n
        y = a(i, 1) + a(j, 1) - 1
    If y > n Then y = y - n
    u(a(i, 2), j) = y
    Next i
Next j
Cells(1).Resize(n, n) = u
End Sub

Thanks for your efforts. I'm interested in a random number generator that works the same as the one in the above thread, but with a different twist, if possible. The Cash 3 lottery game has digits from 0-9 for 3 positions. If you can create a random generator that produces values 10-30 for a cash 3 game and 10-40 for a play 4 game, would be very much appreciated. For the Cash 3, you would have 30 values ranging from 10-30. For the Play 4, you would have 40 values ranging from 10-40. Is this possible to do? no duplicates in either columns or rows.

 

[mirabeau]

maybe like this?
gives 4 randomly selected, nonrepeated, numbers from 10 to 40
the numbers are sorted for you but easy to leave out the sorting feature if you don't want this

Sub xyz()
Dim b(30) As Boolean, x&, k&, c&
Do
    x = Int(Rnd * 31)
    If Not b(x) Then k = k + 1:  b(x) = True
Loop Until k = 4
For x = 0 To 30
   If b(x) Then c = c + 1: Cells(c, 1) = x + 10
Next x
End Sub

 

[CARBOB]

maybe like this?
gives 4 randomly selected, nonrepeated, numbers from 10 to 40
the numbers are sorted for you but easy to leave out the sorting feature if you don't want this

Sub xyz()
Dim b(30) As Boolean, x&, k&, c&
Do
    x = Int(Rnd * 31)
    If Not b(x) Then k = k + 1:  b(x) = True
Loop Until k = 4
For x = 0 To 30
   If b(x) Then c = c + 1: Cells(c, 1) = x + 10
Next x
End Sub

That doesn't work. Not even sure it can be done. The output would look like this, but the values in each column and row, would range from 10 to 39.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	10
2	8	3	5	27	28	11	23	22	12	10
3	19	14	16	8	9	22	4	3	23	21
4	14	9	11	3	4	17	29	28	18	16
5	27	22	24	16	17	30	12	11	1	29
6	9	4	6	28	29	12	24	23	13	11
CASH 3

Excel 2007

 

[mirabeau]

That doesn't work. Not even sure it can be done. The output would look like this, but the values in each column and row, would range from 10 to 39.

It doesn't work coz of great ambiguity about just what it was that you wanted to "work".

If you want to select 30 non-repeating integers from 10 to 30, then no, that's not possible.

Otherwise, since I'm not a mind-reader, perhaps you could be clearer about just what you're looking for?

Is it some 10 by 6 table, with 1 to 10 along the top and 50 randomized integers underneath?

So why should you want some VBA to do this?

 

[gardnertoo]

That's a terrific solution to the original problem, Mirabeau. Very elegant, no brute force required. That's why I love this board, I often learn something new.
ray:

 

[mirabeau]

That's a terrific solution to the original problem, Mirabeau. Very elegant, no brute force required. That's why I love this board, I often learn something new.
ray:

Thanks for the feedback gardnertoo. I really appreciate that.

That code I wrote a year or so back. It was just taking up space on my hard drive so I thought someone else may as well get some interest or possible further use from it.

(As you probably know) it's a type of construct called a "Latin Square". Has use in (statistical) experimental design and probably other applications as well.

 

[CARBOB]

It doesn't work coz of great ambiguity about just what it was that you wanted to "work".

If you want to select 30 non-repeating integers from 10 to 30, then no, that's not possible.

Otherwise, since I'm not a mind-reader, perhaps you could be clearer about just what you're looking for?

Is it some 10 by 6 table, with 1 to 10 along the top and 50 randomized integers underneath?

So why should you want some VBA to do this?

Thanks for you efforts.