Choose a random number from an array, then eliminate

brumby

Active Member
Joined
Apr 1, 2003
Messages
398
dunno if i give this the right title.

What I am after is this :

I have a list of numbers ( 1-100 ), in column A.

I want to choose a random number from that list ( eg 59 ).

I then want to be able to show in an another column, numbers 1 to 100, but not showing number 59.

I want to repeat this 10 times, so each time, choosing a random number, then producing a list, but not showing the number previously selected.

Ive tried for days, but now i am totally beaten.

Many Thanks,
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub buildLst()
'Builds 10 lists, on the active Sheet,each missing one random # each.
'Also displays a MsgBox listing the missing # in each list!

For myRI = 1 To 10

Randomize
'Note: The 100 below = the ending number of the data list.
' The 1 below = the starting number of your data list.
myRanItem = Int((100 * Rnd) + 1)
myItems = myItems & "List: " & myRI & " is missing: " & myRanItem & vbLf

For myDat = 1 To 100
If myDat <> myRanItem Then ActiveSheet.Cells(65536, myRI).End(xlUp).Offset(1, 0).Value = myDat
Next myDat

Next myRI
MsgBox myItems
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello,
this would be my approach
deleting the random item in each column
Code:
Sub test()
'Erik Van Geit
'051216
'data in column A
'copied data in column B, C, D ... "missing" one random item
Dim LR As Long
Dim I As Integer

Const FR As Integer = 1 'first row
Const NC As Integer = 5 'number of columns

LR = Cells(Rows.Count, 1).End(xlUp).Row

Randomize Timer

Range("A" & FR & ":A" & LR).Copy Range("B" & FR & ":B" & LR).Resize(LR - FR + 1, NC)

For I = 1 To NC
Cells(Int((LR - FR + 1) * Rnd + FR), I + 1).Delete shift:=xlUp
Next I

End Sub
kind regards,
Erik
 

brumby

Active Member
Joined
Apr 1, 2003
Messages
398
many thanks,

Will this mean if a number is generated the same number cannot be generated in the next array?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I edited cajones code from the other thread a bit...

see what this code does on an empty sheet
the resulting list could then be used in your project
Code:
Option Explicit

Sub pick_numbers()

Dim I As Long
Dim DR As Long  'row to delete

Const FN = 111  'first number
Const LN = 999  'last number
Const NR = 100  '# of items to pick

    If FN > LN Or NR > LN - FN + 1 Then
    MsgBox "Lowest Number < Highest Number" & Chr(10) & "# items < Highest Number - Lowest Number +1", 48, "ERROR"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns("A:B").ClearContents

    With [A1]
    .Value = 111
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=LN
    End With
Range("B1:B" & LN - FN + 1) = "=RAND()"
[A:B].Sort Key1:=[B1], Order1:=xlAscending, Header:=xlNo
[B:B].ClearContents
Range("A" & NR + 1 & ":A" & LN).Delete Shift:=xlUp

Application.ScreenUpdating = False

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,010
Messages
5,569,602
Members
412,282
Latest member
bobHenk
Top