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]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Okay thanks for the help with the table for me.

 

[kelly mort]

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.

Whatever works cool is okay for me.

 

[DanteAmor]

This is the macro

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
            cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), letter)
            If (cnt = 1 And c.Offset(, -1).Value <> letter) Or cnt = 2 Then exists = True
        Loop
        c.Value = letter
    Next
End Sub

The result will be like this:

<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:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><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 >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:32px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:center; ">D</td><td style="text-align:center; ">B</td><td style="text-align:center; ">F</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">C</td><td style="text-align:center; ">E</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">H</td><td style="text-align:center; ">G</td></tr><tr style="height:32px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">B</td><td style="text-align:center; ">C</td><td style="text-align:center; ">C</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">F</td><td style="text-align:center; ">A</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">I</td><td style="text-align:center; ">I</td></tr><tr style="height:32px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:center; ">G</td><td style="text-align:center; ">G</td><td style="text-align:center; ">I</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">B</td><td style="text-align:center; ">H</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">C</td><td style="text-align:center; ">A</td></tr><tr style="height:32px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:center; ">I</td><td style="text-align:center; ">C</td><td style="text-align:center; ">D</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">F</td><td style="text-align:center; ">G</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">B</td><td style="text-align:center; ">B</td></tr><tr style="height:32px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:center; ">E</td><td style="text-align:center; ">I</td><td style="text-align:center; ">G</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">H</td><td style="text-align:center; ">D</td><td style="background-color:#d8d8d8; "> </td><td style="text-align:center; ">B</td><td style="text-align:center; ">F</td></tr></table>

But it only works with the letters from A to I.
If you want to put fruits or things, you must complete the code to change the letters for fruits. Or add a fruit array in the macro. There are several alternatives, but for now it works with letters.

 

[kelly mort]

Okay very cool!!!


I found a way to replace the the letters with the fruits and it's working cute since I am not good at the arrays yet.

So I wanna know if it is possible to make sure A and B are always more than the others.

Don't worry if that's not cool .

Have a wonderful time.

 

[higrm]

What you can do when you substitute your fruits for letters is sort the letters by number of occurrences and then just make sure your two largest occurrences are the ones you want the most of. No one needs to know that behind the scenes you are changing the representation of the random letters. Create a table that counts the 9 letters. Then create a new table that has the fruit names replacing the letters based on their counts.

 

[kelly mort]

What you can do when you substitute your fruits for letters is sort the letters by number of occurrences and then just make sure your two largest occurrences are the ones you want the most of. No one needs to know that behind the scenes you are changing the representation of the random letters. Create a table that counts the 9 letters. Then create a new table that has the fruit names replacing the letters based on their counts.

Okay thanks.

I appreciate the assistance

 

[higrm]

Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​	H​	I​	J​
2​	D	B	E		C	C		A	I
3​	B	F	D		I	I		G	C
4​	A	E	D		B	H		G	F
5​	C	F	H		D	I		G	E
6​	I	F	G		C	B		D	D
7​
8​
9​	D	6​		Apple
10​	C	5​		Orange
11​	I	5​		Mango
12​	B	4​		Lime
13​	F	4​		Lemon
14​	G	4​		Grape
15​	E	3​		Cherry
16​	A	2​		Banana
17​	H	2​		Peach
18​
19​
20​	Apple	Lime	Cherry		Orange	Orange		Banana	Mango
21​	Lime	Lemon	Apple		Mango	Mango		Grape	Orange
22​	Banana	Cherry	Apple		Lime	Peach		Grape	Lemon
23​	Orange	Lemon	Peach		Apple	Mango		Grape	Cherry
24​	Mango	Lemon	Grape		Orange	Lime		Apple	Apple

Sheet: Sheet4

 

[higrm]

Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​	H​	I​	J​
2​	E	E	G		D	F		A	H
3​	I	A	D		C	G		H	H
4​	B	H	F		G	D		C	A
5​	E	F	I		B	G		H	H
6​	E	B	H		F	A		G	G
7​
8​	Sort by	Count		Fixed list
9​	H	7​		Apple
10​	G	6​		Orange
11​	F	4​		Mango
12​	E	4​		Lime
13​	A	4​		Lemon
14​	D	3​		Grape
15​	B	3​		Cherry
16​	C	2​		Banana
17​	I	2​		Peach
18​
19​
20​	Lime	Lime	Orange		Grape	Mango		Lemon	Apple
21​	Peach	Lemon	Grape		Banana	Orange		Apple	Apple
22​	Cherry	Apple	Mango		Orange	Grape		Banana	Lemon
23​	Lime	Mango	Peach		Cherry	Orange		Apple	Apple
24​	Lime	Cherry	Apple		Mango	Lemon		Orange	Orange
25​
26​
27​	=INDEX($E$9:$E$17,MATCH(B2,$B$9:$B$17,0),1)

Sheet: Sheet4

 

[DanteAmor]

Okay very cool!!!
I found a way to replace the the letters with the fruits and it's working cute since I am not good at the arrays yet.
So I wanna know if it is possible to make sure A and B are always more than the others.
Don't worry if that's not cool .
Have a wonderful time.

Macro updated with fruits or whatever you need:

Sub Randomly()
    Dim r As Range, c As Range, ale As Long, letter As String, exists As Boolean, cnt As Long
    Dim things As Variant
    things = Array("Apple", "Orange", "Mango", "Lime", "Lemon", "Mango", "Melon", "Pine", "Pear")
    
    Set r = Range("B2:D6,F2:G6,I2:J6")
    r.ClearContents
    For Each c In r
        exists = True
        Do While exists
            ale = WorksheetFunction.RandBetween(0, 8)
            'letter = Chr(ale)
            letter = things(ale)
            exists = False
            cnt = WorksheetFunction.CountIf(Range(Cells(c.Row, "B"), Cells(c.Row, "J")), letter)
            If (cnt = 1 And c.Offset(, -1).Value <> letter) Or cnt = 2 Then exists = True
        Loop
        c.Value = letter
    Next
End Sub

So I wanna know if it is possible to make sure A and B are always more than the others.

A and B or A, B, C and D?
Appear more in a row or in the whole matrix?

 

[kelly mort]

A and B or A, B, C and D?
Appear more in a row or in the whole matrix?

Whatever works

If in a row is cool then let's do that

But if the whole matrix is cooler then let's go for it.