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.

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64"]Number[/TD]
[TD="width: 64"]Color[/TD]
[TD="width: 64"]Color[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Name[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Number[/TD]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Plum[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Pink[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Gray[/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Brett[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Aaron[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Kim[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]James[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Pat[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Jeff[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Rob[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Bill[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.