Random Sample without duplicates

I'm working on this random sampling of an invoice for price validation audit. I got help on pulling a random sample of 10% of the total entries, but I'm getting duplicate entries. What can I change in my macro to fix this?



Thank you.

the trick is to keep tally of which numbers have been selected by the random generator.

I sugest to keep track on an array (n1 thru n2) and loop the random generator until it finds a number not yet generated (control array member =0)
once a sutible number is found, the control array member is to be set to 1

In which column are the duplicates?

I've got it pulling column B and F onto the Sample sheet and I need to not get duplicates of column B values. The whole idea is to randomly select items on the invoice for a price audit, but if I get the same item# on there twice the price audit isn't as effectively random.

This code deletes duplicates in column B

Please Login or Register  to view this content.

I'd prefer to leave the source sheet intact as I will need it later. Is there another way to ignore duplicates?

Do you mean you want the result to be to copied it to separate sheet, so that you have input and putput data separately?

Yes, I've got the current macro copying a random 10% sampling into the "Samples" sheet. It only copies the Item# and Unit price which is all I need it to copy, but I want it to not copy more than one of any given Item# each time I run it so that I have a good 10% random sampling. (the 10% is based on number of unique Item#s)

It worked, I just put this at the front of my macro to create this output sheet, then ran my randomizer off the output, then deleted the output at the end. This leaves me with the random 10% sample, based on number of unique item#s, and the input untouched.

Thank you AB33!!

You are welcome!

Crap! I think I'm messing something up when I move all this code into my actual macro. I'm trying to run samples from three different sheets into the same results sheet. I've duplicated the code for each different sheet to pull from and I'm getting a syntax error in x(P, k) = x(i, k)
when the code runs for the second sheet. Any ideas?