Help:how to select random numbers in column

biznez

Board Regular
Joined
Nov 1, 2009
Messages
136
Hello everyone,

I have a list of numbers in A1:A50. I would like to have something that would select 10% of those numbers randomly avoiding duplicates
thanks for all your help
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG18May11
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Randomize
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]Do[/COLOR] Until .Count = 5
    num = Range("A" & Int(Rnd * 50) + 1).value
        [COLOR="Navy"]If[/COLOR] Not .Exists(num) [COLOR="Navy"]Then[/COLOR]
            .Add num, ""
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Loop[/COLOR]
Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
in column b next to your numbers use the =rand() functions and drag it down. then use this formula in column c or in your second sheet referencing the correct columns in sheet 1.


=INDEX(A1:A50, MATCH(SMALL(B1:B50, ROW()), B1:B50, 0))

... where A contains your values of interest, and B contains random numbers.
 
Upvote 0
Try:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG18May11
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Randomize
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]Do[/COLOR] Until .Count = 5
    num = Range("A" & Int(Rnd * 50) + 1).value
        [COLOR="Navy"]If[/COLOR] Not .Exists(num) [COLOR="Navy"]Then[/COLOR]
            .Add num, ""
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Loop[/COLOR]
Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick

Hi Mike, thanks for your help...im trying to execute this macro using commadbutton. would this work?

Private Sub CommandButton1_Click()
Dim num As Integer
Randomize
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Do Until .Count = 5
num = Range("A" & Int(Rnd * 50) + 1).Value
If Not .Exists(num) Then
.Add num, ""
End If
Loop
Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys)
End With
End Sub
 
Upvote 0
in column b next to your numbers use the =rand() functions and drag it down. then use this formula in column c or in your second sheet referencing the correct columns in sheet 1.


=INDEX(A1:A50, MATCH(SMALL(B1:B50, ROW()), B1:B50, 0))

... where A contains your values of interest, and B contains random numbers.

Hi Bryonwoods, i tried that but i get "#NAME?" in those cells in column B and C
 
Upvote 0
Try:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG18May11
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Randomize
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]Do[/COLOR] Until .Count = 5
    num = Range("A" & Int(Rnd * 50) + 1).value
        [COLOR="Navy"]If[/COLOR] Not .Exists(num) [COLOR="Navy"]Then[/COLOR]
            .Add num, ""
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Loop[/COLOR]
Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick

Hi Mike, running this macro gives me Run-Time error '6' Overflow and highlights "num = Range("A" & Int(Rnd * 50) + 1).value" in yellow
 
Upvote 0
Hi Mike, running this macro gives me Run-Time error '6' Overflow and highlights "num = Range("A" & Int(Rnd * 50) + 1).value" in yellow

Sorry Mike, i forgot to mention that each cell contains 9 digits
how would i do that?
 
Upvote 0

Forum statistics

Threads
1,224,591
Messages
6,179,768
Members
452,940
Latest member
rootytrip

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
Back
Top