Random sample in excel WITHOUT duplicates.

[rshukla]

Hello everyone,

I have had too many of my excel troubles solved by the good members of this thread so I feel compelled to contribute my accidental excel accomplishment for which I could not find a solution online (sorry, if I just missed it) - obtaining random samples in excel without duplicates.

The purpose of the attached file is to obtain a true random sample of unique results from any data population of size N. This can’t be done in Excel’s data analysis tool-pack (Data tab > Data Analysis > Random number generation) because in that output, values can be repeated in sample extract of sufficient size proportional to total population, i.e., duplicates appear in random sample.

This file is set to work for a data population of 28 comprising of 26 letters of the English alphabet and 2 intentionally inserted duplicates (letters J and Q) to prove that duplicates will not be included in random sample obtained and that the formulas work as intended. Therefore, the maximum number of unique results that can be obtained in a random sample is 26 (28 letters – 2 duplicates).

1. To refresh random samples of size n=1 to n=26, press F9.

2. To use this file for a larger population:

a. Insert lines as needed below index value 2 since formulas are different for row containing index value 1
b. Copy your selection criteria (names, numbers etc.) in column B
c. Copy formulas down in lines inserted.
d. Make sure numbers in column A are in sequential order.

3. To obtain a sample size larger than 26, simply enter the desired size in row 1 of any column and delete the remaining sample columns.

I hope you’d find this file useful. Your comments / corrections are welcome!

Thanks!