Help:how to select random numbers in column

B

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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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
MickG said:
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
Click to expand...

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
bryonwoods30 said:
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.
Click to expand...

Hi Bryonwoods, i tried that but i get "#NAME?" in those cells in column B and C
 
Upvote 0
MickG said:
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
Click to expand...

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
biznez said:
Hi Mike, running this macro gives me Run-Time error '6' Overflow and highlights "num = Range("A" & Int(Rnd * 50) + 1).value" in yellow
Click to expand...

Sorry Mike, i forgot to mention that each cell contains 9 digits
how would i do that?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
380
Peter_SSs
Peter_SSs
M
  • Question
Randomize as equally possible
2
Replies
11
Views
403
mahmed1
M
C
  • Question
Repetitive ”randomly” assigning/selecting team members for a task based on previous number of selections/assignments.
Replies
0
Views
343
ciprinitu
C
G
  • Solved
Getting a set of random selection from a range
Replies
3
Views
79
Fluff
Fluff
E
  • Question
Macro Based Test Randomizer for Beginners
Replies
1
Views
119
Cubist
Cubist

Forum statistics

Threads
1,215,063
Messages
6,122,935
Members
449,094
Latest member
teemeren

Share this page

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