Duplicate Results when Performing Sampling through the Data Analysis Tab

**greywarden** · 03-22-2018, 07:29 AM

Hello

I am always dealing with duplicate results when performing a random sampling data analysis to a specific numbers range.

Let's assume that Column F in my test files includes a range of document numbers. Whenever I perform a random sampling data analysis asking for 5 samples, it's more than probable that I will always deal with duplicate values.

Is there any way to get unique value samples?

Many thanks in advance

Yannis

**Glenn Kennedy** · 03-22-2018, 08:05 AM

Two ways.

1. volatile. New list generated every time something changes on the sheet. In G3, an array formula copied down:

=INDEX($F$3:$F$49,LARGE(ROW($1:$47)*NOT(COUNTIF($G$2:G2, ROW($1:$47))), RANDBETWEEN(1,48-ROW(A1))))

2. Non-volatile. Doesn't change, unless cell selected and copied down again.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

and in h3, an array formula, copied down:

=INDEX($F$3:$F$49,randbetweena(1,47,0,FALSE))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...