Random generator Excel spreadsheet 2

[DebbieDavis]

Greetings, I found this thread but cannot figure out how to use the vba code. thread707-97374
I have an employee spreadsheet and the boss wants to randomly choose two employees per month for drug testing. Column C has their names, and if they've left, column F has a date so somehow I need to randomly pick 2 people from column C if G is null. I don't have a clue where to start. Many thanks for any helpful thoughts.

 

[VBAjedi]

There's a super-simple way to do this if you just want a quick 30-second solution. In the first empty column next to your employee table, put the formula:

=Rand()

next to each row. This will generate a new random number (between 0 and 1) each time the worksheet is calculated. Each month, just sort on this column and take the first two rows where column G (or did you mean F?) is empty. Note: the act of sorting the data will trigger a recalculate, which in turn will generate new numbers. However, that won't matter because your rows will already be in the new random order you want. If this bugs you, you can always skip the sort and just manually scan the list for the two greatest values.

Let me know if that works for you!

VBAjedi

 

[KenWright]

Or even combine that with a VLOOKUP:-

Assuming names in B2:B1000, as VBAJedi suggested put in A2 =RAND() and copy down, and then in say cell H1 and H2 put

=VLOOKUP(SMALL($A$2:$A$1000,1),$A$2:$B$1000,2,0)
=VLOOKUP(SMALL($A$2:$A$1000,2),$A$2:$B$1000,2,0)

or even

=VLOOKUP(LARGE($A$2:$A$1000,1),$A$2:$B$1000,2,0)
=VLOOKUP(LARGE($A$2:$A$1000,2),$A$2:$B$1000,2,0)

That way you don't need to sort or search, and a simple F9 will change the data. In actual fact you only need to open the workbook and it will change automatically picking out two unfortunates

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DebbieDavis]

Outstanding you guys. Many thanks for that. It's working superbly. Two more questions, though. I need to make some tweaks to the formula. There is a column called "remove from list". If the value is null, the randomization needs to be run. If not, then it needs to skip it. How would I modify this to my criteria?

=VLOOKUP(SMALL($A$2:$A$1000,1),$A$2:$B$1000,2,0)
=VLOOKUP(SMALL($A$2:$A$1000,2),$A$2:$B$1000,2,0)

And also (I can probably search for this), people will be added and deleted so B2:B1000 can flutuate. How can I make B1000 more dynamic? Many thanks again.

 

[xlbo]

have a look at this FAQ regarding dynamic range names:
faq68-1331

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[DebbieDavis]

Thanks xlbo. I'm so ignorant with Excel I wouldn't even know how to use that code. looks complicated. how would I get it into those VLOOKUP statements? It's amazing I got the above suggestions to work as it is. But many thanks anyway.

 

[xlbo]

no code - just formulae
This is the relevant bit:

1. Display the Define Name window (Insert/Name/Define)

2. Enter an appropriate name (could be the columns heading) in the Upper textbox

3. Enter the following formula in the Refers To textbox (This formula assumes a single column list starting in Column A Row 2 with heading in Row 1 on Sheet1 AND no data below the list...

CODE
=OFFSET(INDIRECT("Sheet1!$A$2"),0,0,CountA(Sheet1!$A:$A)-1,1)

If you call the range "myRange" then your formula becomes:
=VLOOKUP(SMALL(myRange,1),myRange,2,0)
=VLOOKUP(SMALL(myRange,2),myRange,2,0)


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[apeoortje]

what you could do it have an extra collum (in this case D) with the formulae

in D1 put:

=COUNTA(C2:C501)

D2 put:

=RAND()

D3 put:

=(D1-1)*D2+2

D4 put:

=(D1-1)*D2+2

D5 put:

=RAND()

D6 put:

=(D1-1)*D4+2


then take the get rid of the decimal points, and then cells

D5 and D3 will give you the row that the employee choosen is in.

 

[DebbieDavis]

Many thanks xlbo and ape. I'll give these a try and don't worry you'll hear from me if I slip and fall. Thanks again!!

 

[Bowers74]

Just in case you are still interested in a macro:

Sub RandomPick()
Dim NewVal As Long, OldVal As Long
Dim lr As Long, i As Long
Dim msg As String
lr = [B65536].End(xlUp).Row - 1
i = 0
While i < 2
    i = i + 1
    NewVal = Int((lr * Rnd) + 2)
    If NewVal <> OldVal And Range("F" & NewVal) = "" Then
        msg = msg & Range("B" & NewVal) & vbLf
    Else
        i = i - 1
    End If
    OldVal = NewVal
Wend
msg = msg & vbLf & "Have been selected for" & vbLf
msg = msg & "the random drug screening!"
MsgBox msg, vbInformation
End Sub

Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here