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

Shums Faruk

Please find attached.

This is how you want?
EE_Example_random_selec_V1.xlsx

Jase Alexander

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

Jase Alexander

ASKER

HI Shums

Sorry meant to say Ive used this in conjunction:-

=INDEX($G3:$G65536,RANDBETWEEN(1,COUNTA($A3:$A65536)),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

Shaun Vermaak

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

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jase Alexander

ASKER

Amazing

Thank you so much for your help

Appreciate the time you've taken on this

J

Shums Faruk

You're Welcome J! Glad I was able to help.