Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Garner, North Carolina, USA
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas for Sets (2000)

    Can Excel do the following: I have 19 names, 1 name in each cell. I want to create 6 groups/lists of 3 names each (1 list would have 4 names). I want a different combination of names in each group so that by the end of 8 or 9 months each name has been associated with all of the other names at least once, with very few repeats. Does this make sense? My reason for needing this list: Management wants all 19 employees (by groups of 3s) to "connect" over lunch once a month. I could manually do this but can Excel make it easier for me?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    If the names are in the range A1:A19 on worksheet Sheet1, then the following code will generate a random list of the names on worksheet Sheet2 grouped into five groups of three and one group of four names each time it is run.

    <pre>Public Sub NewList()
    Dim iNum(1 To 19) As Integer, iMax As Integer
    Dim I As Integer, J As Integer, iRand As Integer
    Worksheets("Sheet2").Range("A:A").ClearContents
    For I = 1 To 19
    iNum(I) = I
    Next I
    Randomize
    iMax = 19
    For I = 1 To 19
    iRand = Int(iMax * Rnd + 1)
    Worksheets("Sheet2").Range("A1").Offset(I - 1, 0).Value = _
    Worksheets("Sheet1").Range("A1").Offset(iNum(iRand ) - 1, 0).Value
    For J = iRand To iMax - 1
    iNum(J) = iNum(J + 1)
    Next J
    iMax = iMax - 1
    Next I
    For I = 15 To 3 Step -3
    Worksheets("Sheet2").Range("A1").Offset(I, 0).EntireRow.Insert
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Garner, North Carolina, USA
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    Legare,
    There is an error when I try and run the code - "Runtime error '1004': Application-defined or object-defined error." When I try to debug it points to this statement:
    Worksheets("Sheet2").Range("A1").Offset(I - 1, 0).Value = _ Worksheets("Sheet1").Range("A1").Offset(iNum(iRand ) - 1, 0).Value
    I don't know VB so I am not able to correct it. Can you help me out? Thanks!

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formulas for Sets (2000)

    Are the sheets in your workbook called Sheet1 and Sheet2?
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formulas for Sets (2000)

    Do you have a sheet1 and sheet2 in your workbook?

    Does sheet1 A1:A19 have the list of 19 names?

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Garner, North Carolina, USA
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    Yes to both. See attachment. Am I doing this right?

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formulas for Sets (2000)

    I don't see anything wrong off hand, but try using this attachment. It is working well in here!
    Regards,
    Rudi

  8. #8
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Garner, North Carolina, USA
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    Thanks, Rudi. That is one list of groups of 3 using all 19 names. Now is it possible to create other groups of three so that eventually all 19 names have been grouped/paired together by 3s?
    Example see attachment

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formulas for Sets (2000)

    Here is a modification of Legare's code that makes 12 random lists. Change the number (iList) to create as many as desired (up to 256).

    Steve

    <pre>Option Explicit
    Public Sub NewList()
    Dim iNum(1 To 19) As Integer, iMax As Integer
    Dim I As Integer, J As Integer, iRand As Integer
    Dim iLists As Integer, x As Integer
    iLists = 12
    Worksheets("Sheet2").Cells.ClearContents

    For x = 1 To iLists
    For I = 1 To 19
    iNum(I) = I
    Next I
    Randomize
    iMax = 19
    For I = 1 To 19
    iRand = Int(iMax * Rnd + 1)
    Worksheets("Sheet2").Range("A1").Offset(I - 1, x - 1).Value = _
    Worksheets("Sheet1").Range("A1").Offset(iNum(iRand ) - 1, 0).Value
    For J = iRand To iMax - 1
    iNum(J) = iNum(J + 1)
    Next J
    iMax = iMax - 1
    Next I
    Next
    For I = 15 To 3 Step -3
    Worksheets("Sheet2").Range("A1").Offset(I, 0).EntireRow.Insert
    Next I
    End Sub</pre>


  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    Just run the macro again. It will erase the list on Sheet2 and create a new list in a different random order.

    I have attached a workbook with a button on Sheet1 labeled "New List". Every time you push this button, you should get a new list on Sheet2.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    I undertand most of the code, but I am not sure what the I=15 to 3 Step -3 is doing. Please explain. What do you do to change the size of the groupings. For example I have a staff of 40 and I want to set up various groups (4, 5, 8, 10 for example), I have to change the 19 to 40, but what other changes would be necessary.

    I am assuming it is tied to the I =15 to 3 Step -3

    Thanks

  12. #12
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    I played around some more and think I have it figured out.

  13. #13
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Garner, North Carolina, USA
    Posts
    241
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    This is great! Thanks so much guys! You went the second mile for me. I really appreciate it!

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas for Sets (2000)

    Starting with 15 will put an empty row after the 15th row. That puts the last 4 people in a group. I then step down 3 rows at a time putting an empty row in front of every third row which groups the rest into groups of three.
    Legare Coleman

Posting Permissions

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