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]

I have no idea of ​​the information of your matrix, then better decide what you need.

 

[kelly mort]

Okay I am going for the row.

So let's do the row.

For A, B, C and D.

That's first four items in the array.

 

[kelly mort]

Hello @DanteAmor,

In post #19 , I have been testing the code and it looks very amazing.


If my last demand is giving you a headache, we can stick to the post #19 .

But one amendment needed to post#19 :

That is for each three consecutive rows, like rows 2 to 4, there should be only one letter in a column within those rows.


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.


Then we select the next range of rows which will be say B6:B8 and we perform the same logic as above.

Then the next range will be B9:B11 in that order.

So far the matrix has become B2:J16.


It seems I am asking for too much .

 

[higrm]

Just out of curiosity, what is the purpose of this exercise? Are you trying to do something, what?, or is this a problem you have been given to solve, share the original problem statement?

 

[kelly mort]

Just out of curiosity, what is the purpose of this exercise? Are you trying to do something, what?, or is this a problem you have been given to solve, share the original problem statement?

Actually the problem statement is what I stated from the beginning with an amendment in post #23 .


I am also curious just as you are. Post #23 is the best shot of what I want to achieve so far.

Just like you pointed out in a post, I am looking for a very tricky way to distribute the items across.

A form of a pattern I wanna attain .

Part of my curiosity journey. I believe that everything is possible so when the idea comes to me I try to figure it out.

Thanks for being here with me.

 

[DanteAmor]

Okay I am going for the row.
So let's do the row.
For A, B, C and D.
That's first four items in the array.

For this requirement, try :

Option Explicit
Sub Randomly()
    Dim r As Range, c As Range
    Dim ale As Long, cnt As Long, n As Long, counter As Long
    Dim things As Variant, letter As String, exists As Boolean
    
    Application.ScreenUpdating = False
    
    things = Array("Apple", "Orange", "Mango", "Lime", "Lemon", "Banana", "Melon", "Pine", "Pear")
    
    Set r = Range("B2:D6,F2:G6,I2:J6")
    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
        Loop
        c.Value = ale
    Next
    For n = 0 To UBound(things)
        r.Replace n, things(n)
    Next
End Sub

With that we closed the 3 original requirements.

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.

 

[kelly mort]

The post #26 is very amazing. I am actually learning a lot from your skills.

Regards

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.

 

[kelly mort]

I'm glad to help you. I appreciate your kind comments.

I am grateful as well .

Please did you look at the post #23 ?

If yes is it possible to be implemented in the code in post #26 ?

 

[DanteAmor]

I am grateful as well .

Please did you look at the post #23 ?

If yes is it possible to be implemented in the code in post #26 ?

I did not understand the requirement in post # 23. Without examples of possible cases it is difficult to understand the requirements.