Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
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:D" & 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).Entir eRow.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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
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 & ":D" & 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).Entir eRow.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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
Thanks again, Joel .... I'll try it tonight or tomorrow morning!
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
On Mon, 21 Apr 2008 10:59:36 -0700 (PDT), Ray
wrote: 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random selections with criteria
Thanks Lars-Åke ... an ingenious solution! I'll give it a try ...
Rgds, ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making random and unique selections from a list | Excel Discussion (Misc queries) | |||
Random selections | Excel Worksheet Functions | |||
random number entry based on two criteria | Excel Discussion (Misc queries) | |||
Top Five selections based on sum of items meeting criteria | Excel Worksheet Functions | |||
Averaging Random Selections? | Excel Programming |