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

 

[mirabeau]

Hello, it is possible to make a lottery of 60/6?
What purpose of this macro? what it shows?
For this macro do?

Perhaps further elucidation of just what you are looking for, and to which macro you refer, would be useful towards your getting a helpful response.

 

[piter]

Hello, mirabean is this macro last from 01 to 36 of 10 columns,
Just switch to other lottery where 01 to 60 is 36 then switch to 60
It is my lottery is 60/6 (mega). Just change the limit of 36 to 60 ok
The macro is this last,
Question, what is this matrix? thank you

 

[piter]

01 a 60=

Sub randum () Dim lJx As Long, lKx As Long, LMX As Long, LNX Como Lox, Long As Long As rRng Dim rRnx Range, como Resize Faixa lKx = 10 Células Cells.Clear (1, 1). (1, lKx ). Formula = "= COLUNA ()" Células (1, lKx + 3) Formula = "= SUM (M2: M37)". Células. (2, lKx + 3) FormulaArray = "= (MAX (CONT.SE (A2: J2, A2: J2))> 1) * 1 "... Cells (2, lKx + 3) Células de preenchimento automático (2, lKx + 3) Resize (36, 1) Cells (1, lKx + 4) Formula =" = SUM (N2: N37) ".. Cells (2, lKx + 4) Resize (36, 1) Formula =" = CONT.SE (A2: J2 "," 0 "" & ROW () -1) 'Pilhas (2, lKx + 4). Resize (36, 1). NumberFormat = "@" Cells (2, 1). Resize (36, lKx). células claras (36 + 2, 1). FormulaArray = "= (MAX (CONT.SE ( A2: A37, A2: A37))> 1) * 1 "Cells (36 + 2, 1) Células de preenchimento automático (36 + 2, 1) Set Resize (1, lKx) rRng = Cells (2, lKx + 1.. ). Resize (36, 2) Células Set rRnx = (2, 1). Resize (36, lKx) Com rRng. Cells (1, 2). Resize (36, 1) Formula. = "right = (" "0 "" Linha & () -1,2) ". Cells (1, 2). redimensionar (36, 1). NumberFormat =" @ "colunas (lKx + 4). ClearFormats. As células (1, 2). redimensionar ( 36, 1) =. Cells (1, 2). Resize (36, 1) Valor.. Cells (1, 1). Resize (36, 1). Formula = "= rand ()". Cells (1, 1 ..) Resize (36, 1) = células (1, 1) Resize (36, 1) Valor Ordenar Key1:.... = células (, 1), Order1: = xlAscending, Cabeçalho _: = xlNo, OrderCustom: = 1, MatchCase: = Orientação, Falso:..... = xlTopToBottom Para lJx = 1 a lKx Cells (1, 1) Fórmula Resize (36, 1) = "= rand ()" Células (1, 1) Resize . (36, 1) = células (1, 1) redimensionar (36, 1) valor de classificação Key1:.... = células (, 1), Order1: = xlAscending, Cabeçalho _: = xlNo, OrderCustom: = 1, MatchCase: = Orientação, Falso:. =. xlTopToBottom Para LMX = Lox 1 a 36 = 0 = Para LNX LMX a 36 células (LNX, 2) copiar células (LMX + 1, lJx) Se Application.WorksheetFunction.CountIf (rRnx. Linhas (LNX), células (LNX + 1, lJx). Valor)> Células 1 Ou. (LNX, 2) = lnx então, as células (LMX + 1, lJx). claras. As células (LNX, 2). copiar células ( 36. + 2, lKx + 2) Linhas (LNX) células claras (LNX + 1, 1) Resize (36 + 2 -..... LNX, 2) copiar células (LNX, 1) = lox lox + 1 Se LNX <36 e um lox <37 - LMX Então lnx = lnx - 1 Else End randum Se Exit Else Para End If Se rRnx.Cells (36, lKx) <> "" Then Exit Sub LNX Próxima Próxima LMX End lJx Próximo With End Sub</PRE><!-- / message -->

 

[piter]

Hello, my lottery changing to 7 columns and 25 rows,
You must change the summary to 7 columns and 25 lines the rest is all the same

 

[dholzman]

Would it be possible to make the first column 1 to 36 in order and have the rest of the table random, this would just to eliminate any number being in order?

I was hoping someone could address this request. I am looking for a similar thing, but not the numbers in the first column in order, just that I can manually decide (hard code) what the first column of numbers should be with the rest of the table randomized to fit the criteria of never repeating within the column and across the columns. Hope that made sense.

 

[mirabeau]

I was hoping someone could address this request. I am looking for a similar thing, but not the numbers in the first column in order, just that I can manually decide (hard code) what the first column of numbers should be with the rest of the table randomized to fit the criteria of never repeating within the column and across the columns. Hope that made sense.

What are the dimensions (rows and columns) of your table?

Are your numbers integers, and what is the range of their values?

 

[dholzman]

What are the dimensions (rows and columns) of your table?

Are your numbers integers, and what is the range of their values?

Sure, n = 11, so it is an 11x11 table with integers of 1-11, no repeating within the column or across the columns. The code you had did this perfectly and now I just want to be able to specify the first column order of the numbers 1-11 (I will do this in the spreadsheet itself rather than in the code) and then let the code generate the remaining 10 columns (no repeating within the column or across the columns).

Thanks again.

 

[mirabeau]

Sure, n = 11, so it is an 11x11 table with integers of 1-11, no repeating within the column or across the columns. The code you had did this perfectly and now I just want to be able to specify the first column order of the numbers 1-11 (I will do this in the spreadsheet itself rather than in the code) and then let the code generate the remaining 10 columns (no repeating within the column or across the columns).

Thanks again.

Dave,

On a blank worksheet, put your 11 non-repeating numbers from A1 downwards.

The run this VBA code and see if it's on the lines of what you want.

Sub testcode()

Const n As Long = 11
Dim i As Long, j As Long
Dim x As Long, y As Long
Dim a(1 To n, 1 To 2) As Long, b
Dim c(1 To n, 1 To n) As Long, u(1 To n, 1 To n) As Long

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

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, j

b = Cells(1).Resize(n)
For i = 1 To n
    For j = 1 To n
        If b(i, 1) = u(j, 1) Then
            For k = 1 To n
                c(i, k) = u(j, k)
            Next k
        End If
    Next j
Next i

Cells(3).Resize(n, n) = c

End Sub

 

[dholzman]

Dave,

On a blank worksheet, put your 11 non-repeating numbers from A1 downwards.

The run this VBA code and see if it's on the lines of what you want.

Sub testcode()

Const n As Long = 11
Dim i As Long, j As Long
Dim x As Long, y As Long
Dim a(1 To n, 1 To 2) As Long, b
Dim c(1 To n, 1 To n) As Long, u(1 To n, 1 To n) As Long

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

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, j

b = Cells(1).Resize(n)
For i = 1 To n
    For j = 1 To n
        If b(i, 1) = u(j, 1) Then
            For k = 1 To n
                c(i, k) = u(j, k)
            Next k
        End If
    Next j
Next i

Cells(3).Resize(n, n) = c

End Sub

YOU ARE AWESOME!!! Now I wish I could understand what is going on in the code so I could figure these things out for myself.

 

[CARBOB]

Can this be adapted for values 0-9 in column A?