Results 1 to 10 of 10

Thread: Randomly generated list with duplicates in column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Randomly generated list with duplicates in column

    Hello,

    My apologies if this has been asked before. I read through several threads and can't seem to find exactly what I need. Basically, in column A, there is a list of locations in groups of two that the names in column B are assigned to in pairs. The list in column B is larger than the locations in column A. I need to find a way to randomly generate a column that takes two names from column B and excludes the second entry in column A so the same name isn't assigned two times in a row nor assigns what was in column C:

    A B C D
    Location Student Previous
    Assignment
    Random Assignment
    Store Front Bob Basement Back
    Store Front Jim Store Front
    Store Back Ben Store Front
    Store Back Sue Attic
    Parking Lot Mary Basement Front
    Parking Lot George Attic
    Basement Front Brittany Basement Back
    Basement Front Fred Parking Lot
    Basement Back Monica Basement Front
    Basement Back Dexter Store Back
    Attic Allen Store Back
    Attic Betty Parking Lot
    Ted
    John
    Megan
    Gerry

    The additional names in column B are excluded if there aren't enough locations from column A but need to be assigned the next time locations are chosen randomly. I hope this makes since. Thank you so much in advance.


  2. #2
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Hi Sdown,
    Here is an idea:

    A
    List of places
    B
    Previous
    C
    Random
    D
    Next
    1 Store Front Store Front 3 Parking Lot
    2 Store Back Store Back 6 Attic
    3 Parking Lot Parking Lot 3 Basement Front
    4 Basement Front Basement Front 6 Attic
    5 Basement Back Basement Back 2 Store Back
    6 Attic Attic 5 Basement Back
    7 Store Front Store Front 5 Basement Back
    8 Store Back 4 Basement Front
    9 Parking Lot 2 Store Back
    10 Basement Front 2 Store Back
    11 Basement Back 2 Store Back
    12 Attic 2 Store Back
    13 Store Front 6 Attic
    14 Store Front 6 Attic
    15 Store Back 1 Store Front
    16 Parking Lot 1 Store Front
    17 Basement Front 6 Attic
    18 Basement Back 4 Basement Front
    19 Attic 1 Store Front
    20 Store Front 3 Parking Lot

    Where
    C2 is =INT(RANDBETWEEN(1,6))
    D2 is
    =IF(INDEX($A$2:$A$7,C2)=B2,INDEX($A$3:$A$8,C2),INDEX($A$2:$A$7,C2))

    Test sheet at: https://1drv.ms/x/s!AovCE1fDrrdSnUaw...o7WrL?e=Idjhqc


    Cheers
    Sergio

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Another option for results in column "D".
    Code:
    Sub MG23Aug15
    Dim RngA As Range, Dn As Range, c As Long, n As Long, Nam As Long, RngB As Range
    Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Dim Dic1 As Object, Dic2 As Object
    
    Set Dic1 = CreateObject("scripting.dictionary")
    Dic1.CompareMode = vbTextCompare
    c = 1
    For Each Dn In RngB: Dic1(Dn.Value) = Dn.Offset(, 1).Value: Next Dn
    
    Set Dic2 = CreateObject("scripting.dictionary")
    Dic2.CompareMode = vbTextCompare
    
    While c < RngA.Count + 1
     n = n + 1
     Nam = Application.RandBetween(1, RngB.Count)
     If Not Dic2.exists(RngB(Nam).Value) Then
         If Not Dic1(RngB(Nam).Value) = Cells(c + 1, "A") Then
            c = c + 1
            Dic2(RngB(Nam).Value) = Empty
            Cells(c, 4) = RngB(Nam)
        End If
    End If
    Wend
    
    End Sub
    Regards Mick

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Hello Sergio,

    Thank you so much for your reply. This is helpful. Is there a way to make sure two people (and only two people) are assigned to one of the places in column A? Thank you again.

    Quote Originally Posted by sergioMabres View Post
    Hi Sdown,
    Here is an idea:

    A
    List of places
    B
    Previous
    C
    Random
    D
    Next
    1 Store Front Store Front 3 Parking Lot
    2 Store Back Store Back 6 Attic
    3 Parking Lot Parking Lot 3 Basement Front
    4 Basement Front Basement Front 6 Attic
    5 Basement Back Basement Back 2 Store Back
    6 Attic Attic 5 Basement Back
    7 Store Front Store Front 5 Basement Back
    8 Store Back 4 Basement Front
    9 Parking Lot 2 Store Back
    10 Basement Front 2 Store Back
    11 Basement Back 2 Store Back
    12 Attic 2 Store Back
    13 Store Front 6 Attic
    14 Store Front 6 Attic
    15 Store Back 1 Store Front
    16 Parking Lot 1 Store Front
    17 Basement Front 6 Attic
    18 Basement Back 4 Basement Front
    19 Attic 1 Store Front
    20 Store Front 3 Parking Lot

    Where
    C2 is =INT(RANDBETWEEN(1,6))
    D2 is
    =IF(INDEX($A$2:$A$7,C2)=B2,INDEX($A$3:$A$8,C2),INDEX($A$2:$A$7,C2))

    Test sheet at: https://1drv.ms/x/s!AovCE1fDrrdSnUaw...o7WrL?e=Idjhqc


    Cheers
    Sergio

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Hello Mick,

    Thank you for the help. When I run that, I get some folks that are assigned to the same area as they were previously that I need to avoid. Any suggestions?

    Quote Originally Posted by MickG View Post
    Another option for results in column "D".
    Code:
    Sub MG23Aug15
    Dim RngA As Range, Dn As Range, c As Long, n As Long, Nam As Long, RngB As Range
    Set RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Dim Dic1 As Object, Dic2 As Object
    
    Set Dic1 = CreateObject("scripting.dictionary")
    Dic1.CompareMode = vbTextCompare
    c = 1
    For Each Dn In RngB: Dic1(Dn.Value) = Dn.Offset(, 1).Value: Next Dn
    
    Set Dic2 = CreateObject("scripting.dictionary")
    Dic2.CompareMode = vbTextCompare
    
    While c < RngA.Count + 1
     n = n + 1
     Nam = Application.RandBetween(1, RngB.Count)
     If Not Dic2.exists(RngB(Nam).Value) Then
         If Not Dic1(RngB(Nam).Value) = Cells(c + 1, "A") Then
            c = c + 1
            Dic2(RngB(Nam).Value) = Empty
            Cells(c, 4) = RngB(Nam)
        End If
    End If
    Wend
    
    End Sub
    Regards Mick
    Last edited by Fluff; Aug 23rd, 2019 at 11:28 AM. Reason: Deleted failed image attempt

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Sorry Mick,

    I tried to insert a snip of the sheet. Sorry it did not show.

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Location Student Previous Assignment Random Assignment
    Store Front Bob Basement Back Allen
    Store Front Jim Store Front Megan
    Store Back Ben Store Front Fred
    Store Back Sue Attic Betty
    Parking Lot Mary Basement Front Ted
    Parking Lot George Attic John
    Basement Front Brittany Basement Back Dexter
    Basement Front Fred Parking Lot Sue
    Basement Back Monica Basement Front Monica
    Basement Back Dexter Store Back Mary
    Attic Allen Store Back Brittany
    Attic Betty Parking Lot Gerry
    Ted
    John
    Megan
    Gerry

  8. #8
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Randomly generated list with duplicates in column


  9. #9
    New Member
    Join Date
    Aug 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Hello Mick,

    Thank you again. Seems closer. This still shows the current location and the previous location the same at times (Bob and Store Front in this example):

    Location Student Previous Assignment Random Assignment
    Store Front Bob Basement Back Megan
    Store Front Jim Store Front Bob
    Store Back Ben Store Front Mary
    Store Back Sue Attic John
    Parking Lot Mary Basement Front Gerry
    Parking Lot George Attic George
    Basement Front Brittany Basement Back Jim
    Basement Front Fred Parking Lot Brittany
    Basement Back Monica Basement Front Dexter
    Basement Back Dexter Store Back Allen
    Attic Allen Store Back Betty
    Attic Betty Parking Lot Ben
    Ted
    John
    Megan
    Gerry

  10. #10
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Randomly generated list with duplicates in column

    Thinking about your problem a bit further
    It appears to me that each time the code is run for a new names in column "D", you need the code to pass the Current "locations" (ref:- previous Random assignments name column "D"), in column "A" to column "C" to sit against the new names in column "D", These will then become to latest "Previous Assignment" .
    Does that make sense to you ????
    Last edited by MickG; Aug 27th, 2019 at 05:30 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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