Randomly Distribution of items across a range with vba

[kelly mort]

I have this table of the range

B2:J6

And I am looking for a way to randomly distribute the letters A-I across.

These are the rules:
1. A row can take just A or A and A . If 2 As, then they should be immediately adjacent each other. Example B2C2.

2. The first four letters can appear more than the last five.

3. Columns E and H are excluded.

I hope this is achievable.

Thanks in advance

 

[kelly mort]

Okay I will try and post the possible outcome here.

Just a moment.

 

[DanteAmor]

Want to post a small screen shot? See Part B here.

 

[kelly mort]

|Col B |Col C |Col D |Col E |Col F |Col G |Col H |Col I |Col J|
| B   |     B   |     G   |          |    G   |     I    |           |    A  |   A    |
| A   |     A   |     E   |          |    D   |     D    |           |    F  |   F    |
| I   |     I   |     F   |          |    A   |     A    |           |    C  |   C    |

| H   |     C   |     C   |          |    B   |     H    |           |    D  |   D    |
| B   |     D   |     H   |          |    E   |     E    |           |    F  |   A    |
| I   |     B   |     G   |          |    G   |     D    |           |    C  |   C    |

| A   |     A   |     E   |          |    D   |     D    |           |    F  |   F    |
| I   |     I   |     G   |          |    A   |     A    |           |    C  |   C    |
| H   |     C   |     C   |          |    B   |     H    |           |    D  |   D    |

| I   |     D   |     H   |          |    E   |     E    |           |    B  |   A    |
| B   |     B   |     G   |          |    G   |     I    |           |    C  |   C    |
| A   |     A   |     E   |          |    D   |     D    |           |    F  |   F    |

| I   |     I   |     G   |          |    A   |     A    |           |    C  |   C    |
| H   |     C   |     C   |          |    B   |     H    |           |    D  |   D    |
| B   |     D   |     H   |          |    E   |     E    |           |    F  |   A    |

So in the groups of three rows as above, a letter should not repeat itself in a column of those three groups.

 

[DanteAmor]

ok, I review it

 

[kelly mort]

ok, I review it

Okay

 

[DanteAmor]

Try this

Option Explicit
Sub Randomly()
    Dim r As Range, c As Range
    Dim ale As Long, cnt As Long, n As Long, counter As Long, countB As Long
    Dim things As Variant, letter As String, exists As Boolean
    Dim rws As Variant, j As Long
    
    Application.ScreenUpdating = False
    
    things = Array("Apple", "Orange", "Mango", "Lime", "Lemon", "Banana", "Melon", "Pine", "Pear")
    
    rws = Array(2, 5, 8, 11, 14)
    Set r = Range("B2:D16,F2:G16,I2:J16")
    r.ClearContents
    For Each c In r
        exists = True
        n = UBound(things)
        Do While exists
            ale = WorksheetFunction.RandBetween(0, n)
            exists = False
            cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), ale)
            If (cnt = 1 And c.Offset(, -1).Value <> ale) Or cnt = 2 Then exists = True
            
            counter = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), ">" & 3)
            If counter > 2 And ale > 3 Then
                exists = True
                n = 3
            End If
            
            If c.Column = 2 Then
                For j = 0 To UBound(rws)
                    If c.Row >= rws(j) And c.Row <= rws(j) + 2 Then
                        countB = WorksheetFunction.CountIf(Range(Cells(rws(j), "B"), Cells(rws(j) + 2, "B")), c.Value)
                        If countB > 1 Then
                            exists = True
                        End If
                        Exit For
                    End If
                Next
            End If
        Loop
        c.Value = ale
    Next
    For n = 0 To UBound(things)
        r.Replace n, things(n)
    Next
    
End Sub

Result:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:24.71px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:24.71px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:24.71px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Pear</td><td style="background-color:#d7e4bc; text-align:center; ">Pine</td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#d7e4bc; text-align:center; ">Lemon</td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#d7e4bc; text-align:center; ">Banana</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Pine</td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">Pine</td><td style="background-color:#ccc0da; text-align:center; ">Lemon</td><td style="background-color:#ccc0da; text-align:center; ">Mango</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Banana</td><td style="background-color:#ccc0da; text-align:center; ">Orange</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Lime</td><td style="background-color:#ccc0da; text-align:center; ">Apple</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">Banana</td><td style="background-color:#ccc0da; text-align:center; ">Apple</td><td style="background-color:#ccc0da; text-align:center; ">Mango</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Lemon</td><td style="background-color:#ccc0da; text-align:center; ">Lime</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Pine</td><td style="background-color:#ccc0da; text-align:center; ">Orange</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">Melon</td><td style="background-color:#ccc0da; text-align:center; ">Mango</td><td style="background-color:#ccc0da; text-align:center; ">Apple</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Lime</td><td style="background-color:#ccc0da; text-align:center; ">Lime</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Banana</td><td style="background-color:#ccc0da; text-align:center; ">Lemon</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#d7e4bc; text-align:center; ">Pine</td><td style="background-color:#d7e4bc; text-align:center; ">Lemon</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#d7e4bc; text-align:center; ">Pear</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td><td style="background-color:#d7e4bc; text-align:center; ">Banana</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Pear</td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Pine</td><td style="background-color:#d7e4bc; text-align:center; ">Lemon</td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Banana</td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">Mango</td><td style="background-color:#ccc0da; text-align:center; ">Mango</td><td style="background-color:#ccc0da; text-align:center; ">Pear</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Apple</td><td style="background-color:#ccc0da; text-align:center; ">Orange</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Banana</td><td style="background-color:#ccc0da; text-align:center; ">Pine</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">Melon</td><td style="background-color:#ccc0da; text-align:center; ">Lemon</td><td style="background-color:#ccc0da; text-align:center; ">Orange</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Mango</td><td style="background-color:#ccc0da; text-align:center; ">Lime</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Apple</td><td style="background-color:#ccc0da; text-align:center; ">Apple</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td style="background-color:#ccc0da; text-align:center; ">Lime</td><td style="background-color:#ccc0da; text-align:center; ">Apple</td><td style="background-color:#ccc0da; text-align:center; ">Lemon</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Banana</td><td style="background-color:#ccc0da; text-align:center; ">Banana</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#ccc0da; text-align:center; ">Orange</td><td style="background-color:#ccc0da; text-align:center; ">Orange</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#d7e4bc; text-align:center; ">Pear</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Orange</td><td style="background-color:#d7e4bc; text-align:center; ">Melon</td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Lemon</td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#d7e4bc; text-align:center; ">Pine</td></tr><tr style="height:23px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#d7e4bc; text-align:center; ">Apple</td><td style="background-color:#d7e4bc; text-align:center; ">Pear</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Mango</td><td style="background-color:#d7e4bc; text-align:center; ">Lime</td><td style="background-color:#dbe5f1; "> </td><td style="background-color:#d7e4bc; text-align:center; ">Banana</td><td style="background-color:#d7e4bc; text-align:center; ">Lemon</td></tr></table>

 

[DanteAmor]

Edit:

Option Explicit
Sub Randomly()
    Dim r As Range, c As Range
    Dim ale As Long, cnt As Long, n As Long, counter As Long, countB As Long
    Dim things As Variant, letter As String, exists As Boolean
    Dim rws As Variant, j As Long
    
    Application.ScreenUpdating = False
    
    things = Array("Apple", "Orange", "Mango", "Lime", "Lemon", "Banana", "Melon", "Pine", "Pear")
    
    rws = Array(2, 5, 8, 11, 14)
    Set r = Range("B2:D16,F2:G16,I2:J16")
    r.ClearContents
    For Each c In r
        exists = True
        n = UBound(things)
        Do While exists
            ale = WorksheetFunction.RandBetween(0, n)
            exists = False
            cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), ale)
            If (cnt = 1 And c.Offset(, -1).Value <> ale) Or cnt = 2 Then exists = True
            
            counter = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), ">" & 3)
            If counter > 2 And ale > 3 Then
                exists = True
                n = 3
            End If
            
            If c.Column = 2 Then
                For j = 0 To UBound(rws)
                    If c.Row >= rws(j) And c.Row <= rws(j) + 2 Then
                 countB = WorksheetFunction.CountIf(Range(Cells(rws(j), "B"), Cells(rws(j) + 2, "B")), [COLOR=#0000ff][B]ale[/B][/COLOR])
                        If countB > [COLOR=#0000ff][B]0[/B][/COLOR] Then
                            exists = True
                        End If
                        Exit For
                    End If
                Next
            End If
        Loop
        c.Value = ale
    Next
    For n = 0 To UBound(things)
        r.Replace n, things(n)
    Next
    
End Sub

 

[kelly mort]

Okay, I have tested the code.

It's cool.

However, some of them are repeating in those groups.

 

[DanteAmor]

Okay, I have tested the code.

It's cool.

However, some of them are repeating in those groups.

You can put an image of the evidence.
Did you try the last code?


According to your requirement, only column B should be verified. I hope you are checking only column B, because if you are checking another column then you have a problem with your requirement. The macro works well for me.

That's for column B with the cells B2, B3, B4 in that range if "A" or any letter appears in a cell in that range B2:B4 then no other cell in that range must have that letter.

 

[kelly mort]

Oh sorry.

I used the column B as an example.

I want to check all validate all the columns.

I am very sorry for not being able to make that completely clear.

I am actually marveled at your dexterity.

I wish you can validate the other columns for me.