Random number based on certain criteria

[Necroscope]

I have a spreadsheet with a list of Invoice numbers (which all begin with INVxxxxxx) in column B and then in column C the name of the person who cleared that Invoice.

What I need to do each week is do a random check on an invoice that each person has cleared so if "John Smith" cleared 10 invoices, I want to randomly pick one of those 10 invoice numbers from the list in column B. It's sorted into date order and there are 30 different people to search through so if there is a way to help via formula or VBA, that would be a big help.

I've messed around with Lookup and Index but haven't got this working.

And if possible, I want to have a new sheet within the same file to display the results. (For example, create an "Accuracy Check" sheet and list the people and put the random invoice number next to their name so that the check can be carried out.)

Can anyone help?

TIA.

 

[footoo]

Here's one possibility.
Assumes that the dates are in column A of the source sheet.

Sub ft()
With Sheets("Accuracy Check")
    .Cells.ClearContents
    Sheets("Sheet1").[A:C].Copy .[A:A] 'Change sheet name required
    .Range([D2], Cells(Rows.Count, 3).End(xlUp)(1, 2)).Formula = "=RAND()"
    .[A:D].Sort Key1:=[D1], Header:=xlYes
    .[D:D].Delete
    .[A:C].RemoveDuplicates Columns:=3, Header:=xlYes
    .[A:C].Sort Key1:=[A1], Header:=xlYes
End With
End Sub

 

[Scott T]

If you want a non VBA solution

Sheet1
[TABLE="class: grid, width: 136"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]invoice[/TD]
[TD]cleared by[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]INV000001[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]INV000002[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]INV000003[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]INV000004[/TD]
[TD]Bert[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]INV000005[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]INV000006[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]INV000007[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]INV000008[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]INV000009[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]INV000010[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]INV000011[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]INV000012[/TD]
[TD]Bert[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]INV000013[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]INV000014[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]INV000015[/TD]
[TD]Bert[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]INV000016[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]INV000017[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]INV000018[/TD]
[TD]Rick[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]INV000019[/TD]
[TD]Rick[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="class: grid, width: 136"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Inv to check[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rick[/TD]
[TD]INV000007[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]INV000011[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mike[/TD]
[TD]INV000008[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bert[/TD]
[TD]INV000012[/TD]
[/TR]
</tbody>[/TABLE]

In B2 of sheet 2
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will Put {} around the formula. Copy down

=INDEX(Sheet1!$B$2:$B$20,SMALL(IF(Sheet1!$C$2:$C$20=A2,ROW(Sheet1!$C$2:$C$20)-ROW(Sheet1!$C$2)+1),RANDBETWEEN(1,COUNTIF(Sheet1!$C$2:$C$20,A2))))

 

[Necroscope]

Hi footoo. Thanks for that - it works great.

Another thought had just occurred to me (apologies for not thinking of this first time round). The 30 people in question each belong to Team 1, Team 2 or Team 3. This info is in column E of the Invoice sheet (Sheet1). Would it be possible to add to your existing code to either list all the team 1, 2 and 3 people together OR even just only list team 1 people?

 

[Necroscope]

Hi Scott. This also works great for me so thank you for taking the time to reply and help me out. It's much appreciated.

 

[footoo]

Another thought had just occurred to me (apologies for not thinking of this first time round). The 30 people in question each belong to Team 1, Team 2 or Team 3. This info is in column E of the Invoice sheet (Sheet1). Would it be possible to add to your existing code to either list all the team 1, 2 and 3 people together OR even just only list team 1 people?

Sub ft2()
Application.ScreenUpdating = False
With Sheets("Accuracy Check")
    .Cells.ClearContents
    Sheets("Sheet1").[A:C].Copy .[A:A]
    Sheets("Sheet1").[E:E].Copy .[D:D]
    .Range([E2], Cells(Rows.Count, 3).End(xlUp)(1, 3)).Formula = "=RAND()"
    .[A:E].Sort Key1:=[E1], Header:=xlYes
    .[E:E].Delete
    .[A:D].RemoveDuplicates Columns:=3, Header:=xlYes
    .[A:D].Sort Key1:=[D1], Key2:=[A1], Header:=xlYes
End With
Application.ScreenUpdating = True
End Sub

 

[Necroscope]

Hi footoo. Thanks for your help with this; I really appreciate you taking the time to respond.