Random selections with criteria

[Ray]

Hi -

I suppose the Subject sounds a bit contradictory, but there is a
method to the madness ....

What I'd like to do is set up a simple auditing tool to select
(mostly) random entries to be audited. Here's the setup:

Cell B1 - the % of entries (ie Lines) to be audited
Cell C1 - total number of entries audited (calc'd from A1, rounded UP)
Cell D1 - Invoice Limit (default = 1)
Cell E1 - Amount limit (default = $25,000)

Cell B5:B? -- invoice numbers (non-sequential)
Cell C5:C? -- # of invoices submitted to date
Cell D5? -- Amount of Invoice

The data comes as a 'dump' from another system and will vary in qty
from audit to audit. NOTE that not all of the audited entries should
be randomly chosen -- ALL entries where the # of Invoices (col c) is
<= the Invoice Limit (cell D1) should be chosen, as well as all
entries where the Invoice Amount (col D) is >= the Amount Limit (cell
E1). The user will enter the % of entries to audit, with the code
first selecting the 'pre-defined' items and then randomly selecting
the rest. An example will help clarify...

Say there are 1000 entries, we want to audit 12% of the entries, using
the default Invoice & Amount limits. The sheet would look like this:
B1 = 12%
C1 = 120 (calculated from A1 x #_of_Entries)
D1 = 1
E1 = $25,000

# of Entries where # of Invoices <=Limit --> 15 entries
# of Entries where Invoice Amount >= $25,000 --> 17 entries
(assume no overlap in these two ... code should select # of Invoices
first, THEN Invoice Amount)

The code would then RANDOMLY select 88 entries. Selected rows (ALL of
them) should have a * put in column A.

How would you code for this?

THANKS VERY MUCH ...!
br//ray

 

[Joel]

See comments in code below. I assume the percentage a percentage after the
filter criteria is met. I filter the data on sheet 1 and copy the data that
mets your criteria to sheet 2. I then add a random number to all the rows
and sort by the random number. Then I delete the rows from sheet two that
don't need to be audited.


Sub audit()

With Sheets("Sheet1")
Percentage = .Range("B1")

LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Set InvoiceRange = .Range("B5" & LastRow)
.Range("D" & LastRow).AutoFilter
'filter data
InvoiceRange.AutoFilter _
Field:=1, _
Criteria1:=">=" & .Range("D1"), _
Operator:=xlAnd, _
Criteria2:="<=" & .Range("E1")
'copy filtered data to sheet 2
InvoiceRange.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("A1")
End With
With Sheets("Sheet2")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
'get number of rows to audit
NumberofRows = WorksheetFunction.RoundUp(LastRow * Percentage, 0)
'add random number to column E to sort filtered list
For RowCount = 1 To LastRow
.Range("E" & RowCount) = Rnd()
Next RowCount
'sort filetered list by random number
.Cells.Sort _
key1:=Range("E1"), _
order1:=xlAscending
'delete rows not needed in audit from sheet 2
.Rows((NumberofRows + 1) & ":" & Rows.Count).Delete
End With
End Sub

 

[Ray]

Thanks Joel ... I'll give it a try!

One question: the % of entries audited should INCLUDE the pre-
filtered lines. So, if we want 12% (cell b1) of 1000 entries, that's
120 lines to be audited. If the pre-filter (# of Invoices & Invoice
Amts) yields 20 lines, 100 entries should be randomly selected. If
the pre-filter yields 30 lines, then 90 rows would be selected. How
would I modify your code to use this logic?

Thanks again,
ray

 

[Joel]

I changed the code to make the percentage the total number of rows not the
filtered rows. I also change the percentage calculation to divide the
percentage by 100. Not sure if the percentage is a fraction (.10) or a whole
number (10).

Sub audit()

With Sheets("Sheet1")

FirstRow = 5
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Percentage = .Range("B1")
'get number of rows to audit
NumberofRows = WorksheetFunction.RoundUp( _
(LastRow - FirstRow + 1) * Percentage / 100, 0)
msgbox("Number of rows to Audit = " & NumberofRows)

Set InvoiceRange = .Range("B" & FirstRow & "" & LastRow)
.Range("D" & LastRow).AutoFilter
'filter data
InvoiceRange.AutoFilter _
Field:=1, _
Criteria1:=">=" & .Range("D1"), _
Operator:=xlAnd, _
Criteria2:="<=" & .Range("E1")
'copy filtered data to sheet 2
InvoiceRange.SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=Sheets("Sheet2").Range("A1")
End With
With Sheets("Sheet2")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row

'add random number to column E to sort filtered list
For RowCount = 1 To LastRow
.Range("E" & RowCount) = Rnd()
Next RowCount
'sort filetered list by random number
.Cells.Sort _
key1:=Range("E1"), _
order1:=xlAscending
'delete rows not needed in audit from sheet 2
.Rows((NumberofRows + 1) & ":" & Rows.Count).Delete
End With
End Sub

 

[Ray]

Thanks again, Joel .... I'll try it tonight or tomorrow morning!

 

[Lars-Åke Aspelin]

Hi -

I suppose the Subject sounds a bit contradictory, but there is a
method to the madness ....

What I'd like to do is set up a simple auditing tool to select
(mostly) random entries to be audited. Here's the setup:

Cell B1 - the % of entries (ie Lines) to be audited
Cell C1 - total number of entries audited (calc'd from A1, rounded UP)
Cell D1 - Invoice Limit (default = 1)
Cell E1 - Amount limit (default = $25,000)

Cell B5:B? -- invoice numbers (non-sequential)
Cell C5:C? -- # of invoices submitted to date
Cell D5? -- Amount of Invoice

The data comes as a 'dump' from another system and will vary in qty
from audit to audit. NOTE that not all of the audited entries should
be randomly chosen -- ALL entries where the # of Invoices (col c) is
<= the Invoice Limit (cell D1) should be chosen, as well as all
entries where the Invoice Amount (col D) is >= the Amount Limit (cell
E1). The user will enter the % of entries to audit, with the code
first selecting the 'pre-defined' items and then randomly selecting
the rest. An example will help clarify...

Say there are 1000 entries, we want to audit 12% of the entries, using
the default Invoice & Amount limits. The sheet would look like this:
B1 = 12%
C1 = 120 (calculated from A1 x #_of_Entries)
D1 = 1
E1 = $25,000

# of Entries where # of Invoices <=Limit --> 15 entries
# of Entries where Invoice Amount >= $25,000 --> 17 entries
(assume no overlap in these two ... code should select # of Invoices
first, THEN Invoice Amount)

The code would then RANDOMLY select 88 entries. Selected rows (ALL of
them) should have a * put in column A.

How would you code for this?

THANKS VERY MUCH ...!
br//ray

If you want to try a solution without macro, here is a suggestion:

Use column E as a helper column. In cell E5 enter the following:

= RAND()+(C5<=$D$1)+(D5>=$E$1)

Copy this down as far as you have rows with invoice data
Note that all rows that qualify via the limit conditions will have
a random number bigger than 1 and the other rows all have random
numbers less than 1. This will guarantee the first mentioned rows to
be included in the audit selection.

In cell A5 enter the following:'

=IF(E5>=LARGE(E$5:E$65535,C$1),"*","")

Copy this down as far as you have rows with invoice data

This should give an asterisk (*) in the a column for each of
the rows with a random number among the greatest.
All the rows with either # invoices equal to or below the # limit
or the amount equal to or more than the amount limit will
be included in this set as long (as the percentage allows).
And rows which do not qualify via the limit condition fill up the set
of selected rows randomly to fulfil the specified percentage.

A new (partly) random selection is presented each time the chart is
recalculated but you may copy columns A to D and Paste Special
them with their values into a separate chart for further handling.

Lars-Åke

 

[Ray]

Thanks Lars-Åke ... an ingenious solution! I'll give it a try ...

Rgds, ray