+ Reply to Thread
Results 1 to 2 of 2

Duplicate Results when Performing Sampling through the Data Analysis Tab

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2013
    Posts
    6

    Duplicate Results when Performing Sampling through the Data Analysis Tab

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Duplicate Results when Performing Sampling through the Data Analysis Tab

    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...
    Attached Files Attached Files
    Glenn



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 07-28-2015, 09:58 AM
  2. performing what if analysis
    By mlew in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 11-30-2012, 05:17 PM
  3. filtering data from multi sheets then performing functions on results
    By jrtaylor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2009, 03:54 PM
  4. Performing sums based on duplicate data
    By supermario2k8 in forum Excel General
    Replies: 2
    Last Post: 05-02-2008, 03:56 PM
  5. Statistical Analysis - Sampling
    By Max55 in forum Excel General
    Replies: 1
    Last Post: 08-21-2006, 06:50 AM
  6. [SOLVED] how do I report data of court cases ,results,analysis,.....
    By Ziad NT in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-12-2006, 06:45 PM
  7. How to refresh data analysis tool results
    By stips2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2005, 05:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1