# 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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### 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
161
Replies
7
Views
162
Replies
1
Views
429
Replies
22
Views
410
Replies
15
Views
319

1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

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