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

 

[DanteAmor]

You can put an image or a matrix showing a couple of possible results

 

[kelly mort]

You can put an image or a matrix showing a couple of possible results

I am browsing on phone and uploading screenshots has been a headache for me since day 1.

So far I think any result will be okay provided rules #1 and #3 are in place.

#2 is not that necessary for now.

Regards

 

[DanteAmor]

Try:

Sub Randomly()
    Dim r As Range, c As Range, ale As Long, letter As String, exists As Boolean, cnt As Long


    Set r = Range("B2:D6,F2:G6,I2:J6")
    r.ClearContents
    For Each c In r
        exists = True
        Do While exists
            ale = WorksheetFunction.RandBetween(65, 73)
            letter = Chr(ale)
            exists = False
            If letter = "A" Then
                cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), letter)
                If (cnt = 1 And c.Offset(, -1).Value <> "A") Or cnt = 2 Then exists = True
            End If
        Loop
        c.Value = letter
    Next
End Sub

 

[kelly mort]

Try:

Sub Randomly()
    Dim r As Range, c As Range, ale As Long, letter As String, exists As Boolean, cnt As Long


    Set r = Range("B2:D6,F2:G6,I2:J6")
    r.ClearContents
    For Each c In r
        exists = True
        Do While exists
            ale = WorksheetFunction.RandBetween(65, 73)
            letter = Chr(ale)
            exists = False
            If letter = "A" Then
                cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), letter)
                If (cnt = 1 And c.Offset(, -1).Value <> "A") Or cnt = 2 Then exists = True
            End If
        Loop
        c.Value = letter
    Next
End Sub

Okay the third rule is working perfectly but the second is having a few issues

1. A letter is appearing more that twice in a row. But the max should be 2.
2. When they are only two some of them are not immediately adjacent. That is if cell C2 has a letter say I and there should be another I, then it should be in Cell D2 but not any other cell.


Regards

 

[DanteAmor]

That's why I asked you for a couple of examples.


You only asked to validate the letter "A"

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

but the second is having a few issues

You eliminated the second rule.

#2 is not that necessary for now.

You can upload the examples
You can upload the examples to dropbox

Or write the examples

 

[kelly mort]

Okay let me try my best to write something :

|Col B |Col C |Col D |Col E |Col F |Col G |Col H |Col I |Col J|
| 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 I am looking for something in this pattern.


Also those A -Z will change later to things like:

1. Apple
2. Orange
3. Mango
4. Lime
5. Lemon
Etc.

Regards

 

[higrm]

So, to codify what you want:
In any row, you can have maximum 2 times the same letter and if you do have a letter 2 times, they must be adjacent. You really only need to figure out one row, since there is no connection between rows, you only need to repeat the same rule you applied to the first line. I think we can calculate the total number of viable solutions for 1 line as 9*9*8*8*7*7*6=1,524,096 Since there are 5 rows, any row can have the same 1,524,096 possible formations.
Wait, no there are fewer viable solutions, as there is another constraint, because in the 3rd column you only have 8 possible values if the first 2 have the same value or if you can match the value in the second column. That is a bit trickier to program. Column 3 can be any value except the value in column 1, (if the value in column 2 = the value in column 1, we've already excluded this.) Interesting. I think this means that we still have 9*9*8, but the 4th column options are less than 8, so most likely 9*9*8*7*6*5*4 is the number of viable options. Still a few.
I am eager to see any solutions to this.
Cheers,
Higrm

PS as you only have 9 letters in your universe, it would be simpler to treat these as the digits 1 through 9 and then later translate the range into letters, fruits or anything your heart desires.

 

[MARK858]

No suggestion just a another version of the table in post number 8 as it doesn't paste well in Excel.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1		B	B	G		G	I		C	C
2		A	A	E		D	D		F	F
3		I	I	G		A	A		C	C
4		H	C	C		B	H		D	D
5		B	D	H		E	E		F	A
Sheet1

 

[higrm]

Ignore my calculation on the maximum number of possible combinations. I didn't take into account that you can duplicate 2 letters in the first 4 columns, which means there are still 7 possible letters for the 5th column.