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

	B	C
1	invoice	cleared by
2	INV000001	Rick
3	INV000002	Bob
4	INV000003	Mike
5	INV000004	Bert
6	INV000005	Rick
7	INV000006	Mike
8	INV000007	Rick
9	INV000008	Mike
10	INV000009	Mike
11	INV000010	Rick
12	INV000011	Bob
13	INV000012	Bert
14	INV000013	Rick
15	INV000014	Rick
16	INV000015	Bert
17	INV000016	Rick
18	INV000017	Rick
19	INV000018	Rick
20	INV000019	Rick

<tbody>
</tbody>

Sheet 2

	A	B
1	Name	Inv to check
2	Rick	INV000007
3	Bob	INV000011
4	Mike	INV000008
5	Bert	INV000012

<colgroup><col><col></colgroup><tbody>
</tbody>

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.