# Thread: Formulas for Sets (2000)

1. ## 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. ## 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>

3. ## 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. ## Re: Formulas for Sets (2000)

Are the sheets in your workbook called Sheet1 and Sheet2?

5. ## 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. ## Re: Formulas for Sets (2000)

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

7. ## Re: Formulas for Sets (2000)

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

8. ## 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. ## 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. ## 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.

11. ## 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. ## Re: Formulas for Sets (2000)

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

13. ## Re: Formulas for Sets (2000)

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

14. ## 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.

#### Posting Permissions

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