Unique numbers in a random list

[Mel Smith]

Hello Folks,

I'm trying to generate random lists (between 1 and 25) on a range of cells in Column A 1:25 but I cannot seem to get unique numbers in my "random" column. Does anyone know how I can achieve this so that each refresh of the screen will generate a new set of numbers against each name that are unique?

Many thanks.

Mel

 

[hurgadion]

Hi,
try the macro:

Sub RandomTwentyFive()
Dim i&, j&

tbl = RndInt(25)
Cells(1, 1).Resize(25) = Application.Transpose(tbl)
 
End Sub


Function RndInt(a As Integer)
Dim V() As Variant, Val As Variant
Dim i&, j&, r&, c&
Dim t1 As Variant, t2 As Variant

Randomize
ReDim V(1 To a)
ReDim Val(1 To 2, 1 To a)
For i = 1 To a
  Val(1, i) = Rnd
  Val(2, i) = i
Next i
For i = 1 To a
  For j = i + 1 To a
    If Val(1, i) > Val(1, j) Then
      t1 = Val(1, j)
      t2 = Val(2, j)
      Val(1, j) = Val(1, i)
      Val(2, j) = Val(2, i)
      Val(1, i) = t1
      Val(2, i) = t2
     End If
   Next j
 Next i
  
i = 0
For r = 1 To a
    i = i + 1
    V(i) = Val(2, i)
Next r
RndInt = V
End Function

The function RndInt is WalkenBach's function, best regards.

 

[Mel Smith]

Perfect!

Many thanks, Mel

 

[snb_]

or

Sub snb()
  Randomize
  ReDim sn(24, 1)

  For j = 0 To UBound(sn)
    sn(j, 0) = Rnd
  Next

  Range("A1:A25") = sn
  Range("A1:A25") = [index(rank(A1:A25,A1:A25),)]
End Sub

 

[hurgadion]

snb_: I think, that Your Solution is quicker, better and simpler

 

[AD_Taylor]

Or to do it with the (now obvious thanks to snb ) formula method:

Excel 2010
	A	B
1	15	0.218586
2	23	0.064959
3	2	0.946286
4	20	0.187018
5	1	0.976534
6	16	0.206239
7	18	0.18945
8	17	0.190598
9	22	0.118541
10	7	0.594407
11	12	0.360737
12	10	0.408105
13	19	0.188167
14	25	0.028503
15	6	0.864623
16	11	0.376866
17	5	0.877075
18	24	0.043601
19	21	0.129761
20	4	0.90479
21	8	0.579807
22	13	0.299599
23	3	0.917331
24	9	0.57296
25	14	0.2541
Sheet2

Cell Formulas
Range	Formula
A1	=RANK(B1,B:B )
B1	=RAND()

 

[Mel Smith]

Thanks folks. Some good ideas now all sorted. Many thanks!

Mel