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

aruen

New Member
Joined
Aug 26, 2013
Messages
8
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.

NumberColorColorNameNameName

<tbody>
</tbody>

Number1
Number2
Number3
Number4
Number5
Number6
Number7
Number8
Number9
Number10
ColorBlack
ColorBlue
ColorYellow
ColorOrange
ColorRed
ColorGreen
ColorPlum
ColorPurple
ColorPink
ColorGray
ColorWhite
NameAdam
NameJoe
NameBrett
NameAaron
NameJerry
NameKim
NameJames
NamePat
NameJeff
NameRob
NameBill

<tbody>
</tbody>
 

aruen

New Member
Joined
Aug 26, 2013
Messages
8
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

New Member
Joined
Aug 17, 2015
Messages
5
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

New Member
Joined
Aug 26, 2013
Messages
8
I don't have all the combinations yet. That's what I am looking for.
 

kalak

Active Member
Joined
Jun 10, 2015
Messages
487
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.
Code:
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
Code:
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

New Member
Joined
Aug 26, 2013
Messages
8
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

New Member
Joined
Aug 17, 2015
Messages
5
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

Active Member
Joined
Jun 10, 2015
Messages
487
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.
 

Forum statistics

Threads
1,082,323
Messages
5,364,579
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top