# Help with retrieving random samples from a dataset

#### vebeenator

##### New Member

As i am learning the traits of excel, i have stumbled up on a problem.

I have the numbers i want to work with organised in cell (d5:d56), i now want to select 26 of these numbers randomly
and organise them in ascending order in a row with no possibility of retrieving duplicates, furthermore i want to retrieve the remaining 26 numbers from (d5:d56) and organise them
in another row.

Any help will be highly appreciated.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### lrobbo314

##### Well-known Member
Something like this.

Book4
DEF
4Rand NumsOutput
5789
67013
71715
83824
97528
108432
119033
123234
136940
148541
153350
168051
175160
187861
191369
202870
214175
226578
236179
243480
255181
266084
27985
2810088
298190
3040100
3115
3224
3384
3450
3588
3679
Sheet1

VBA Code:
``````Sub UNIQUE26NODUPES()
Dim AR() As Variant: AR = Range("D5:D" & Range("D" & Rows.Count).End(xlUp).Row).Value2
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim tmp As Integer: tmp = 0
Dim r As Integer: r = 0

For i = 1 To UBound(AR)
tmp = AR(i, 1)
If Not AL.contains(tmp) Then AL.Add (tmp)
Next i

Do Until AL.Count <= 26
r = Int((AL.Count) * Rnd())
AL.removeat r
Loop

AL.Sort
Range("F5").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray)

End Sub``````

Replies
5
Views
136
Replies
7
Views
132
Replies
1
Views
367
Replies
22
Views
394
Replies
15
Views
306

1,127,709
Messages
5,626,411
Members
416,183
Latest member
IanA

### 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.

### Which adblocker are you using?

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