+ Reply to Thread
Results 1 to 12 of 12

Random Sample without duplicates

  1. #1
    Registered User
    Join Date
    01-31-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2003
    Posts
    10

    Random Sample without duplicates

    I'm working on this random sampling of an invoice for price validation audit. I got help on pulling a random sample of 10% of the total entries, but I'm getting duplicate entries. What can I change in my macro to fix this?



    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Random Sample without duplicates

    the trick is to keep tally of which numbers have been selected by the random generator.

    I sugest to keep track on an array (n1 thru n2) and loop the random generator until it finds a number not yet generated (control array member =0)
    once a sutible number is found, the control array member is to be set to 1

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Random Sample without duplicates

    In which column are the duplicates?

  4. #4
    Registered User
    Join Date
    01-31-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Random Sample without duplicates

    I've got it pulling column B and F onto the Sample sheet and I need to not get duplicates of column B values. The whole idea is to randomly select items on the invoice for a price audit, but if I get the same item# on there twice the price audit isn't as effectively random.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Random Sample without duplicates

    This code deletes duplicates in column B

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-31-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Random Sample without duplicates

    I'd prefer to leave the source sheet intact as I will need it later. Is there another way to ignore duplicates?

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Random Sample without duplicates

    Do you mean you want the result to be to copied it to separate sheet, so that you have input and putput data separately?

  8. #8
    Registered User
    Join Date
    01-31-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Random Sample without duplicates

    Yes, I've got the current macro copying a random 10% sampling into the "Samples" sheet. It only copies the Item# and Unit price which is all I need it to copy, but I want it to not copy more than one of any given Item# each time I run it so that I have a good 10% random sampling. (the 10% is based on number of unique Item#s)

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Random Sample without duplicates

    Please Login or Register  to view this content.
    Last edited by AB33; 02-04-2013 at 05:45 PM.

  10. #10
    Registered User
    Join Date
    01-31-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Random Sample without duplicates

    It worked, I just put this at the front of my macro to create this output sheet, then ran my randomizer off the output, then deleted the output at the end. This leaves me with the random 10% sample, based on number of unique item#s, and the input untouched.

    Thank you AB33!!

  11. #11
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Random Sample without duplicates

    You are welcome!

  12. #12
    Registered User
    Join Date
    01-31-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Random Sample without duplicates

    Crap! I think I'm messing something up when I move all this code into my actual macro. I'm trying to run samples from three different sheets into the same results sheet. I've duplicated the code for each different sheet to pull from and I'm getting a syntax error in x(P, k) = x(i, k)
    when the code runs for the second sheet. Any ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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