Random generator with no duplicates

[anwaee2]

I have row C5:L5 and column B6:B15 that I would like a macro that has a random generator place numbers 0 thru 9 in each cell with no duplicates in the row and no duplicates in the column. Is this even possible?

 

[DanteAmor]

It can be with formulas:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4			0.91	0.16	0.24	0.69	0.63	0.25	0.91	0.6	0.13	0.47
5			0	8	7	2	3	6	1	4	9	5
6	0.61	4
7	0.77	1
8	0.56	5
9	0.39	6
10	0.73	2
11	0.25	9
12	0.79	0
13	0.29	7
14	0.28	8
15	0.67	3
Formula

Cell Formulas
Range	Formula
A6:A15,C4:L4	C4	=RAND()
C5:L5	C5	=RANK(C4,$C$4:$L$4)-1
B6:B15	B6	=RANK(A6,$A$6:$A$15)-1

-------------------------------------------
I also give you the option with macro:

Sub random9()
  Dim arr As Variant
  Dim i As Long, x As Long, y As Long
  Randomize
  
  ReDim arr(0 To 9, 1 To 1)
  For i = 0 To UBound(arr)
    arr(i, 1) = i
  Next
  
  For i = 0 To UBound(arr)
    x = Int(UBound(arr) * Rnd + 1)
    y = arr(i, 1)
    arr(i, 1) = arr(x, 1)
    arr(x, 1) = y
  Next i
  Range("B6").Resize(UBound(arr)).Value = arr
  
  For i = 0 To UBound(arr)
    x = Int(UBound(arr) * Rnd + 1)
    y = arr(i, 1)
    arr(i, 1) = arr(x, 1)
    arr(x, 1) = y
  Next i
  Range("C5").Resize(1, UBound(arr)).Value = Application.Transpose(arr)
End Sub

 

[anwaee2]

Thank you, this is exactly what I needed in a macro. One small problem, the macro works great except it does not put a number in cell L5 or B15, All other cells are as they should be, random numbers with no duplicates.
Again, thank you so much. I thought if it could be done, someone on Mr. Excel would find a way to do it.

 

[DanteAmor]

One small problem, the macro works great except it does not put a number in cell L5 or B15

Try the following macro, I only made a small adjustment:

Sub random9()
  Dim arr As Variant
  Dim i As Long, x As Long, y As Long
  Randomize
 
  ReDim arr(0 To 9, 1 To 1)
  For i = 0 To UBound(arr)
    arr(i, 1) = i
  Next
 
  For i = 0 To UBound(arr)
    x = Int(UBound(arr) * Rnd + 1)
    y = arr(i, 1)
    arr(i, 1) = arr(x, 1)
    arr(x, 1) = y
  Next i
  Range("B6").Resize(UBound(arr) + 1).Value = arr
 
  For i = 0 To UBound(arr)
    x = Int(UBound(arr) * Rnd + 1)
    y = arr(i, 1)
    arr(i, 1) = arr(x, 1)
    arr(x, 1) = y
  Next i
  Range("C5").Resize(1, UBound(arr) + 1).Value = Application.Transpose(arr)
End Sub

----- --

 

[DanteAmor]

There is another option, using only one loop

Sub random9b()
  Dim ar1 As Variant, ar2 As Variant
  Dim i As Long, x1 As Long, x2 As Long, y1 As Long, y2 As Long
  Randomize
  
  ar1 = [row(1:10)]
  ar1(10, 1) = 0
  ar2 = [row(1:10)]
  ar2(10, 1) = 0
  
  For i = 1 To UBound(ar1)
    x1 = Int(UBound(ar1) * Rnd + 1)
    x2 = Int(UBound(ar2) * Rnd + 1)
    y1 = ar1(i, 1)
    y2 = ar2(i, 1)
    ar1(i, 1) = ar1(x1, 1)
    ar1(x1, 1) = y1
    ar2(i, 1) = ar2(x2, 1)
    ar2(x2, 1) = y2
  Next i
  Range("B6").Resize(UBound(ar1)).Value = ar1
  Range("C5").Resize(1, UBound(ar2)).Value = Application.Transpose(ar2)
End Sub