Random sample in excel WITHOUT duplicates.

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!

Hi Marvin,

For 'a' deck of cards, your method will work. However, what if your population comprised of 10 similar decks of cards and you wanted to choose 52 unique values? This is where the method you described has a shortcoming - it will give you a random sample of 52 cards, but it will inevitably comprise of some duplicate cards.

For statistical sampling, presence of duplicates is not always desired and sometimes not to have duplicates is an explicit requirement. That's what my file attempts to do - remove duplicates, if present, in a random sample extraction.

Thanks!

Martin,

Interesting that you pointed out that "Rand()" can produce duplicates. My solution was to increase the decimal places (x.xxxxxxxxxx...xxx instead of just x.xx) but it helps me to have a formula that would avoid this (remote) possibility. Although, it will take me some time to decipher what you've done there...

Thanks!

found another way to generate random numbers without duplicates
let the first cell be a heading e.g in A1 =Random Generated
have the second cell like this e.g in A2 =RandBetween(1,20)
the third cell like this e.g in A3 =LARGE(ROW($1:$20)*NOT(COUNTIF($A$2:A2,ROW($1:$20))),RANDBETWEEN(1,(20+2-1)-ROW(A2)))
press CTRL + SHIFT + ENTER
copy paste to the next row until the desired number of samples were generated.


TAKE NOTE OF THE $ SIGN AND CTRL + SHIFT + ENTER
ALSO, THE NUMBERS 1 AND 20 COULD BE CHANGED DEPENDING UPON THE NUMBER OF POPULATION

:D