Help with retrieving random samples from a dataset

vebeenator

New Member
Joined
Apr 1, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi thread members :)

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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,138
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,384
Messages
5,635,982
Members
416,891
Latest member
Okomomo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top