Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Non Repeating Output List from Excel Equal to Input list

    I have this list in a column... 20 different values... I want to generate a random output list from the input list and not repeat. I have used =INDEX($D$1:$D$20,RANDBETWEEN(1,20)) in each of the 20 cells but I get repeaters. The values in Col D are all alpha numeric. Is there something else I need to add to make sure that none of the outputs repeat? I am attaching a file in case you need to see. Look for RandQR_B.xlsx Thanx so much for your assistance.

    DennisK
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    This is a 2-column process in Excel.
    Create a column with the numbers from 1 to 20.
    Create another column next to it with =rand() filled down all 20 rows.
    Then, sort the column with the random numbers but include the first column in the sort.
    Then use that rearrangement of the first column for your selection of your data in D.

    I'm sure someone will send you a UDF that will bypass this issue.
    Attached Files Attached Files
    Last edited by kweaver; 2015-04-03 at 20:31.

  3. The Following User Says Thank You to kweaver For This Useful Post:

    Maudibe (2015-04-04)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    dlkorinek,

    Consider this approach:
    1. Column D1:D20 has your values
    2. Cell H1 has the formula =COUNTIF(G$1:G1,G1)>1 copied down to H20. This column will yield True for repeat or False for unique for the adjacent cell in column G which is populated with numbers 1-20 by running the code.
    3. The code places a random number from 1-20 in each cell in column G until its adjacent cell turns False (validating it is unique)
    4. Cell E1 has the formula =INDEX($D$1:$D$20,G1,1) copied down to E20 which looks at the unique numbers in Col G and indexes the values in col D.

    HTH,
    Maud

    Index1.png

    Place in a standard module:
    Code:
    Public Sub Unique()
    Dim cell As Range, rng As Range
    Set rng = Range("D1:D20")
    For Each cell In rng
        cell.offset(0, 3) = WorksheetFunction.RandBetween(1, 20)
        Do While cell.offset(0, 4) = True
            cell.offset(0, 3) = WorksheetFunction.RandBetween(1, 20)
        Loop
    Next cell
    End Sub
    Columns G and H can be hidden for cosmetic reasons or placed on a different page (the code will need some readjustment). Also, the functions of both columns G and H could have been incorporated into the code but it would have been much more complex and difficult to follow.
    Attached Files Attached Files

  5. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Blue Springs, Missouri, USA
    Posts
    108
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks so much for your input... was hoping to avoid vba as the person I am doing this for wants it as simple as possible but will take a look at it.

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Once you have it set up, it is seamless and works well for repetitive sampling. However, if this is a one time process, KW's approach might be the best for you but as he states, you must copy and paste special the rand values or they will recalculate with the next sheet calculation.

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    The attached file uses 1 line of code.
    Just switch to [Sheet2] and then back to [Sheet1] to see the new random list in column [E].

    The code on [Sheet2] is:
    Code:
    Private Sub Worksheet_Activate()
    [a1].Sort key1:=[b1]
    End Sub
    This method uses kweavers suggestion in post#, with a hidden column [B] for the random number.

    zeddy
    Attached Files Attached Files

Posting Permissions

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