selecting an item at random for QA sampling

[Eric F.]

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.

 

[Eric F.]

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(INDIRECT(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.