VBA to randomly select record in table based on specific criteria

[seaottr]

I have a table of records (named "Table1") and I want VBA to randomly return 1 account number (table header is called "FA_ID") from the table where the cell value in the table column "Product Count" equals 3 and the cell value in the table column "Updated By" equals the value in cell "AZ1".

I have found similar requests in other forums, but all of them aren't quite what I'm looking for (they return multiple records, aren't in tables, they create new worksheets, etc.). I just want the randomly selected account number that meets these conditions gets entered into cell "AZ3".

The number of records in the table constantly changes.

Any help would be greatly appreciated!

 

[Eric W]

First, you can do it with a formula:

Book2
	AT	AU	AV	AW	AX	AY	AZ
1	FA_ID	Product Count	Updated By	Amount			Bob
2	1	1	Al	1			Random account
3	2	2	Ed	2			11
4	3	3	Bob	3
5	4	1	Al	4
6	5	2	Ed	5
7	6	3	Bob	6
8	7	1	Al	7
9	8	2	Bob	8
10	9	3	Ed	9
11	10	1	Bob	10
12	11	3	Bob	11
13
Sheet7

Cell Formulas
Range	Formula
AZ3	AZ3	=INDEX(Table1[FA_ID],AGGREGATE(15,6,(ROW(Table1[FA_ID])-ROW(INDEX(Table1[FA_ID],1))+1)/(Table1[Product Count]=3)/(Table1[Updated By]=AZ1),RANDBETWEEN(1,COUNTIFS(Table1[Product Count],3,Table1[Updated By],AZ1))))

If you still want VBA, like if you don't want it to change every time something changes on the sheet, you can try this macro:

Sub test2()
Dim a() As Variant, i As Long

    ReDim a(0 To 0)
    For i = 1 To Range("Table1").Rows.Count
        If WorksheetFunction.Index(Range("Table1[Product Count]"), i) = 3 And _
           WorksheetFunction.Index(Range("Table1[Updated by]"), i) = Range("AZ1") Then
           ReDim Preserve a(0 To UBound(a) + 1)
           a(UBound(a)) = WorksheetFunction.Index(Range("Table1[FA_ID]"), i)
        End If
    Next i
    
    Range("AZ3") = a(Int(Rnd() * UBound(a) + 1))
End Sub

This actually looks a bit awkward to me, since I use the WorksheetFunction.Index(Range( construct. I'd bet that there's a more elegant way to work with tables in VBA, but I don't know it offhand. Anyway, it should do what you want. Let us know.

 

[DanteAmor]

Here is another macro for you to consider

Sub RandomlyRecord()
  Dim a As Variant, tbl As ListObject
  Dim i As Long, j As Long, c1 As Long, c2 As Long, c3 As Long
  
  Set tbl = ActiveSheet.ListObjects("Table1")
  c1 = tbl.ListColumns("FA_ID").Range.Column
  c2 = tbl.ListColumns("Product Count").Range.Column
  c3 = tbl.ListColumns("Updated By").Range.Column
  
  ReDim a(1 To tbl.DataBodyRange.Rows.Count, 1 To 1)
  For i = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange(i, c2) = 3 And tbl.DataBodyRange(i, c3) = Range("AZ1") Then
      j = j + 1
      a(j, 1) = tbl.DataBodyRange(i, c1)
    End If
  Next
  Range("AZ3") = a(Int(Rnd() * j + 1), 1)
End Sub

 

[seaottr]

Thank you so much DanteAmor! This works perfectly!!! Really appreciate the speedy response!!!

 

[seaottr]

Thank you so much Eric W! I ended up going with DanteAmor's solution as it was a little easier for me to comprehend, but I really appreciate the speedy reply!!! You rock!!!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Just a slight word of caution. Either of the suggested vba codes above need a 'Randomize' statement before the use of the Rnd() function (example in my code below) otherwise each time you open Excel and run the code with the same data you would get the same sequence of values in AZ3. That is, the values will not be truly random.

If interested, here is a non-looping way to get the random FA_ID

Sub SelectRandomID()
  Dim a As Variant

  a = Filter(Application.Transpose(Evaluate(Range("Table1[FA_ID]").Address & "&""|""&" & Range("Table1[Product Count]").Address & "&""|""&" & Range("Table1[Updated By]").Address & "&""|""")), "|3|" & Range("AZ1").Value & "|")
  If UBound(a) = -1 Then a = Split("|")
  Randomize
  Range("AZ3").Value = Split(a(Int(Rnd() * (UBound(a) + 1))), "|")(0)
End Sub

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 with the FILTER function, then here is another formula method too.

seaottr 2020-07-14 1.xlsm
	AT	AU	AV	AW	AX	AY	AZ
1	FA_ID	Product Count	Updated By	Amount			Bob
2	1	1	Al	1			Random account
3	2	2	Ed	2			6
4	3	3	Bob	3
5	4	1	Al	4
6	5	2	Ed	5
7	6	3	Bob	6
8	7	1	Al	7
9	8	2	Bob	8
10	9	3	Ed	9
11	10	1	Bob	10
12	11	3	Bob	11
13
Sheet1

Cell Formulas
Range	Formula
AZ3	AZ3	=INDEX(FILTER(Table1[FA_ID],(Table1[Product Count]=3)*(Table1[Updated By]=CS1)),RANDBETWEEN(1,COUNTIFS(Table1[Product Count],3,Table1[Updated By],CS1)))