Random selections with criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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:D? -- 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
.

Relevant Pages

  • Re: Random selections with criteria
    ... -- Amount of Invoice ... NOTE that not all of the audited entries should ... first selecting the 'pre-defined' items and then randomly selecting ...
    (microsoft.public.excel.programming)
  • Re: track who accessed/deleted a file/folder.
    ... You can enable auditing of object access and then audit specific files/folders for ... but it does generate a huge amount of ... entries so you may want to use it sparingly. ... > have setup audit in win2k, ...
    (microsoft.public.win2000.security)
  • Re: SUM on 1 uniques record from primary table
    ... in this way I see the correct amountpaid for each invoice but the Grand total in the report footer should display the total of all amountspaid for all invoices. ... now it displays only the last amount paid. ...
    (microsoft.public.access.reports)
  • Re: How to "POST" accounting data into underlying tables
    ... charges to be added to an invoice. ... One option would be to put a SetValue macro on the After Update event of ... This assumes that Invoice Number is the name of the combobox, and Amount ...
    (microsoft.public.access.macros)
  • Re: Get Sum Amount from SubForm
    ... Instead of referring to TotInvoiceAmt in your code, ... I have a Mainform (Invoice) with a Subform with details. ... control name 'Amount ' and in the Form footer I have a control TotAmt ... many times I can press the PayButton before going to the ...
    (microsoft.public.access.forms)