Unique numbers in a random list

Mel Smith

Well-known Member
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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi,
try the macro:
Code:
``````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.

Perfect!

Many thanks, Mel

or
Code:
``````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``````

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

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

Excel 2010
AB
1150.218586
2230.064959
320.946286
4200.187018
510.976534
6160.206239
7180.18945
8170.190598
9220.118541
1070.594407
11120.360737
12100.408105
13190.188167
14250.028503
1560.864623
16110.376866
1750.877075
18240.043601
19210.129761
2040.90479
2180.579807
22130.299599
2330.917331
2490.57296
25140.2541
Sheet2
Cell Formulas
RangeFormula
A1=RANK(B1,B:B )
B1=RAND()

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

Mel

Replies
3
Views
457
Replies
5
Views
189
Replies
2
Views
477
Replies
2
Views
321
Replies
3
Views
294

1,203,070
Messages
6,053,363
Members
444,657
Latest member
jessejames1of3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back