How to randomize a group of numbers, colors and names from a list without repeating them in the line.

[aruen]

I need to come up with all the combinations for the below data. I have numbers, colors and names. I need to put one number, two colors and three names in a row without repeating a name or color in each row. I would also like to not replicate the rows with the colors or names in a different order. The top row on my output sheet would have the headers as seen below and then I have a data tab which lists the possible inputs. It would be great to also know how to limit the value of the entire row to say 10 if each input had a value of 1-5. If that is not possible, I can delete the rows with values greater than 10. I would like to do all this within VBA code.

Number

Color

Color

Name

Name

Name

<tbody>
</tbody>

Number	1
Number	2
Number	3
Number	4
Number	5
Number	6
Number	7
Number	8
Number	9
Number	10
Color	Black
Color	Blue
Color	Yellow
Color	Orange
Color	Red
Color	Green
Color	Plum
Color	Purple
Color	Pink
Color	Gray
Color	White
Name	Adam
Name	Joe
Name	Brett
Name	Aaron
Name	Jerry
Name	Kim
Name	James
Name	Pat
Name	Jeff
Name	Rob
Name	Bill

<tbody>
</tbody>

 

[aruen]

bump

 

[aruen]

Quick thought, would this be better fit to have the data in Access, create every possible combo, then delete ones that break any rules I have in place? I am going to start working on that version. Also, if anyone knows where to start, would this be a do while loop with quite a bit of code inside?

 

[Tanya_Z]

If you have all combinations, and you just need to choose one randomly, I would recommend assign them a value using a RAND() function, and then choose MAX or MIN.

 

[aruen]

I don't have all the combinations yet. That's what I am looking for.

 

[kalak]

I don't have all the combinations yet. That's what I am looking for.

OK. So you want VBA and all the combinations.
Regarding data layout, I copied your Numbers list into Cols A &B, your Colors list into cols C&D and your Names list into Cols E&F.
I only give combinations of Colors and Names. You only want one number at a time which is trivial.
So, here's 2 codes that you can run one after the other, which specifically rely on the Colors and Names being in the columns as indicated above.
These should fit your requirement "I need to put ... two colors and three names in a row without repeating a name or color in each row. I would also like to not replicate the rows with the colors or names in a different order."
I leave you to work out any subsequent details for yourself.

Sub colors()

Dim a, y(), clr()
Dim i&, j&, r&, u&, x&, s&
'u  is number of colors on the color list
v& = 2 '2 colors to be selected from the color list
a = Range("D1", Range("D1").End(4))
u = UBound(a)
s = Application.Combin(u, v) + 1

ReDim y(1 To s, 1 To v)
ReDim clr(1 To s, 1 To v)

For i = 1 To u ^ v
    r = r + 1
    For j = 0 To v - 1
        If j = 0 Then
            y(r, 1) = 1 + i Mod u
            clr(r, 1) = a(y(r, 1), 1)
        Else
            x = 1 + Int((i - 1) / (u ^ j)) Mod u
            If x > y(r, j) Then
                y(r, j + 1) = x
                clr(r, j + 1) = a(x, 1)
            Else
                r = r - 1: Exit For
            End If
        End If
    Next j
Next i
[h1].Resize(r, v) = clr
End Sub

Sub names()

Dim a, y(), nam()
Dim i&, j&, r&, u&, v&, x&, s&
'u is number of names on the list
v = 3 '3 names to be selected from the names list
a = Range("F1", Range("F1").End(4))
u = UBound(a)
s = Application.Combin(u, v) + 1

ReDim y(1 To s, 1 To v)
ReDim nam(1 To s, 1 To v)

For i = 1 To u ^ v
    r = r + 1
    For j = 0 To v - 1
        If j = 0 Then
            y(r, 1) = 1 + i Mod u
            nam(r, 1) = a(y(r, 1), 1)
        Else
            x = 1 + Int((i - 1) / (u ^ j)) Mod u
            If x > y(r, j) Then
                y(r, j + 1) = x
                nam(r, j + 1) = a(x, 1)
            Else
                r = r - 1: Exit For
            End If
        End If
    Next j
Next i
[k1].Resize(r, v) = nam
End Sub

 

[aruen]

Hey Kalak,

I cant seem to get the code to output anything. When I stepped through it, I saw that the code was pulling "Blue". Also, the reason I need colors is because I want every combo with every number as well. Another question, is it possible to assign values for each and then not allow the row to exceed a max value?

Thanks!

 

[Tanya_Z]

I don't do VBA, but I can tell you how to do in Access using query. You need to add your table five times. No joins, it's full Cartesian on all of them. Filter the first for number, second and the third for color, the last three for names. Add all of outputs as separate columns, so one combination is a row of five data points. Then, I would recommend that you produce the product, and after that query it one more time to remove those with same names or colors.

 

[kalak]

Hey Kalak,

I cant seem to get the code to output anything. When I stepped through it, I saw that the code was pulling "Blue".

Thanks!

Did you have your colors listed down Col D as I specified?
With "black" in cell D1, "blue" in D2, "yellow" in D3 etc. Orange in D4 etc.

Then run the "colors" code as a standard module VBA.

Likewise run the "names" code with names "Adam" in F1, "Joe" in F2 etc.

If you do so those codes should produce all combinations of colors, and of names.