Jase Alexander
asked on
VBA or formula to pick random selection of numbers except those with date assigned
HI Experts
I hope you can help
I have a simple list of part numbers (in column A) and the ability to enter a date (in column B) against the entry.
Ive been trying to formulate a way I which to select 5 random numbers from the list but with criteria attached.
Basically, if there is a date against one of the selected numbers (it has already been checked for inventory accuracy), it is disregarded and another number is pulled that does not have date against it.
Ive experimented with various Index, Match, and lookup combinations with additional columns inserted to house copied data but I cannot seem to get this to work correctly.
I have attached a sample file which is very basic but fit for purpose. Column G just contains some sample data.
Any ideas on code or a formula (maybe array?) would be greatly appreciated.
Many Thanks
J
EE_Example_random_selec.xlsx
I hope you can help
I have a simple list of part numbers (in column A) and the ability to enter a date (in column B) against the entry.
Ive been trying to formulate a way I which to select 5 random numbers from the list but with criteria attached.
Basically, if there is a date against one of the selected numbers (it has already been checked for inventory accuracy), it is disregarded and another number is pulled that does not have date against it.
Ive experimented with various Index, Match, and lookup combinations with additional columns inserted to house copied data but I cannot seem to get this to work correctly.
I have attached a sample file which is very basic but fit for purpose. Column G just contains some sample data.
Any ideas on code or a formula (maybe array?) would be greatly appreciated.
Many Thanks
J
EE_Example_random_selec.xlsx
ASKER
HI Shums
Thank you for the response
Is there something in Column H that can look at the results in Column G and just select five of those filtered results at random?
With this, I think this could work ..... ?
J
Thank you for the response
Is there something in Column H that can look at the results in Column G and just select five of those filtered results at random?
With this, I think this could work ..... ?
J
ASKER
HI Shums
Sorry meant to say Ive used this in conjunction:-
=INDEX($G3:$G65536,RANDBET WEEN(1,COU NTA($A3:$A 65536)),1)
But if it hits a blank that's in Column G it just displays a blank where as would it be possible to include something in the formula that skips to the next available result?
Ive attached the sheet with your formula and the random pick formula and if you keep running it you start to see blanks.
Ultimately I need five part numbers in Column H to check ??
J
EE_Example_random_selec_V2.xlsx
Sorry meant to say Ive used this in conjunction:-
=INDEX($G3:$G65536,RANDBET
But if it hits a blank that's in Column G it just displays a blank where as would it be possible to include something in the formula that skips to the next available result?
Ive attached the sheet with your formula and the random pick formula and if you keep running it you start to see blanks.
Ultimately I need five part numbers in Column H to check ??
J
EE_Example_random_selec_V2.xlsx
You can record a macro
Sub GetFiveEntries()
Columns("G:G").Select
Selection.Copy
Range("H1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H1").Select
Application.CutCopyMode = False
Selection.ClearContents
Columns("H:H").Select
ActiveSheet.Range("$H$1:$H$28").RemoveDuplicates Columns:=1, Header:=xlYes
Columns("H:H").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H28") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("H1:H28")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("H7").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("H7:H300").Select
Selection.ClearContents
Columns("H:H").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H29") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("H1:H29")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("I15").Select
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Amazing
Thank you so much for your help
Appreciate the time you've taken on this
J
Thank you so much for your help
Appreciate the time you've taken on this
J
You're Welcome J! Glad I was able to help.
This is how you want?
EE_Example_random_selec_V1.xlsx