Duplicate Sample Selection

[Virgo_anil]

I am trying to select random sampled invoices for auditing based on population size (say 10% of population) and certain parameters based on the formulas. However I am getting duplicate samples some of the time. Can someone please review attached file Cell Q1047720 and onwards and suggest changes to the formula to avoid duplicate sample selection.

Please bear in mind below: -
1. Population size will vary month on month and so the sample size
2. 10% sample of overall population is based on certain parameters (Research related and more than $2000) so the formula in O1047720 and P1047720 onwards are required
3. Certain rows will need manual elimination through Macros in Row 1 to 100000 therefore these rows cannot be used to place formula for sample selection

Thanks for your help in advance.

Regards
Anil

[Virgo_anil]

Can anyone help please?

[Greg M]

Hi there,

Here's a cheap 'n' cheerful approach!

Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Please note that the very first Index No in the random list might be duplicated, but if you use only Invoice Nos from row two onwards they should be unique.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Virgo_anil]

This is Amazing.. Thanks a lot.

[Greg M]

Hi again,

Many thanks for your very prompt feedback.

You're welcome - glad I was able to help.

Regards,

Greg M

[josephteh]

Another solution is to generate random numbers using RAND for each row of records, then pick up the records with the smallest or largest random numbers in ascending or descending sequence. There may be a chance that the random numbers generated may contain duplicates and a chance that the duplicate records got picked up in our samples, but it's probably very remote.

To change the sample size, change the value in cell R3.