If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
selecting an item at random for QA sampling
A co-worker has asked for help speeding up a process for QA of images of
scanned documents. She does an end-of-line QA check on a set number of document images selected from a report in a simple spreadsheet. The numbers of significance to her from this report are in columns A to C and these are Batch #, DCN (document control number), and Image Count. The report has a varying number of batches reported on it, though typically there are thousands of batches. The DCN listed for each batch is the first document control number in the batch. The rest of the images in a batch have sequential DCNs with the total number of images in the batch reported in the Image Count column. She selects 10 images at random to review to verify the image is legible. Only the DCN is needed to retrieve an image for review. Her existing process, which she wants to keep (but speed up) is to select a random batch and then from the batch select a random DCN. She repeats this to get 10 DCNs to review. I believe I can set up something for her in a somewhat clumsy way that would be much quicker than her present method but not as simple as I would like. I think it should be possible to set up a formula in a single cell that does all the steps and returns a single randomly selected DCN from a randomly selected batch. Copying the formula to 9 more cells would give her the 10 DCNs to verify. I have looked at some similar questions posted here and have a rough idea of how this might be done but not enough to put anything together that would give her something she could paste into a new report and get 10 DCNs to review without doing anything more. Thanks for any suggestions you may have. |
#2
|
|||
|
|||
selecting an item at random for QA sampling
As a followup, if anyone is interested:
I can generate the the random DCNs by using two columns. In column E, I have entered a formula that picks a row at random and adds the column reference for the DCN numbers: ="b"&RANDBETWEEN(2,MATCH(LOOKUP(2,1/(A$1:A$65535""),A:A),A:A,0)) In column F, I have entered a formula that uses the cell reference in E to set the uppler and lower limits of the RANDBETWEEN function which in turn gives me a random number between the DCN from the row with the randomly chosen batch and the last DCN in that batch: =RANDBETWEEN(INDIRECT(E2),INDIRECT(E2)+OFFSET(INDI RECT(E2),0,1)) I copy these two formulas down 9 more rows to get the 10 random DCNs. This seems to work (at least I can't find an instance when it appears to give an incorrect value). Is there a better way to do this? Is there a way to do this in one cell, ? I can't think of any way to accomplish this in one cell other than with a macro or UDF. Thanks. "Eric F." wrote: A co-worker has asked for help speeding up a process for QA of images of scanned documents. She does an end-of-line QA check on a set number of document images selected from a report in a simple spreadsheet. The numbers of significance to her from this report are in columns A to C and these are Batch #, DCN (document control number), and Image Count. The report has a varying number of batches reported on it, though typically there are thousands of batches. The DCN listed for each batch is the first document control number in the batch. The rest of the images in a batch have sequential DCNs with the total number of images in the batch reported in the Image Count column. She selects 10 images at random to review to verify the image is legible. Only the DCN is needed to retrieve an image for review. Her existing process, which she wants to keep (but speed up) is to select a random batch and then from the batch select a random DCN. She repeats this to get 10 DCNs to review. I believe I can set up something for her in a somewhat clumsy way that would be much quicker than her present method but not as simple as I would like. I think it should be possible to set up a formula in a single cell that does all the steps and returns a single randomly selected DCN from a randomly selected batch. Copying the formula to 9 more cells would give her the 10 DCNs to verify. I have looked at some similar questions posted here and have a rough idea of how this might be done but not enough to put anything together that would give her something she could paste into a new report and get 10 DCNs to review without doing anything more. Thanks for any suggestions you may have. |
Thread Tools | |
Display Modes | |
|
|