Randomly Distribution of items across a range with vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
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
 
No suggestion just a another version of the table in post number 8 as it doesn't paste well in Excel.

Excel 2010
ABCDEFGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]F[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]D[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]A[/TD]

</tbody>
Sheet1

Okay thanks for the help with the table for me.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
This is the macro


Code:
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]D[/td][td]B[/td][td]E[/td][td][/td][td]C[/td][td]C[/td][td][/td][td]A[/td][td]I[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]B[/td][td]F[/td][td]D[/td][td][/td][td]I[/td][td]I[/td][td][/td][td]G[/td][td]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]A[/td][td]E[/td][td]D[/td][td][/td][td]B[/td][td]H[/td][td][/td][td]G[/td][td]F[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]C[/td][td]F[/td][td]H[/td][td][/td][td]D[/td][td]I[/td][td][/td][td]G[/td][td]E[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]I[/td][td]F[/td][td]G[/td][td][/td][td]C[/td][td]B[/td][td][/td][td]D[/td][td]D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]D[/td][td]
6​
[/td][td][/td][td]Apple[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]C[/td][td]
5​
[/td][td][/td][td]Orange[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]I[/td][td]
5​
[/td][td][/td][td]Mango[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]B[/td][td]
4​
[/td][td][/td][td]Lime[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]F[/td][td]
4​
[/td][td][/td][td]Lemon[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]G[/td][td]
4​
[/td][td][/td][td]Grape[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]E[/td][td]
3​
[/td][td][/td][td]Cherry[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]A[/td][td]
2​
[/td][td][/td][td]Banana[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]H[/td][td]
2​
[/td][td][/td][td]Peach[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20
[/td][td]Apple[/td][td]Lime[/td][td]Cherry[/td][td][/td][td]Orange[/td][td]Orange[/td][td][/td][td]Banana[/td][td]Mango[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21
[/td][td]Lime[/td][td]Lemon[/td][td]Apple[/td][td][/td][td]Mango[/td][td]Mango[/td][td][/td][td]Grape[/td][td]Orange[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22
[/td][td]Banana[/td][td]Cherry[/td][td]Apple[/td][td][/td][td]Lime[/td][td]Peach[/td][td][/td][td]Grape[/td][td]Lemon[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23
[/td][td]Orange[/td][td]Lemon[/td][td]Peach[/td][td][/td][td]Apple[/td][td]Mango[/td][td][/td][td]Grape[/td][td]Cherry[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24
[/td][td]Mango[/td][td]Lemon[/td][td]Grape[/td][td][/td][td]Orange[/td][td]Lime[/td][td][/td][td]Apple[/td][td]Apple[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
Upvote 0
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td]E[/td][td]E[/td][td]G[/td][td][/td][td]D[/td][td]F[/td][td][/td][td]A[/td][td]H[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]I[/td][td]A[/td][td]D[/td][td][/td][td]C[/td][td]G[/td][td][/td][td]H[/td][td]H[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]B[/td][td]H[/td][td]F[/td][td][/td][td]G[/td][td]D[/td][td][/td][td]C[/td][td]A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]E[/td][td]F[/td][td]I[/td][td][/td][td]B[/td][td]G[/td][td][/td][td]H[/td][td]H[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]E[/td][td]B[/td][td]H[/td][td][/td][td]F[/td][td]A[/td][td][/td][td]G[/td][td]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td=bgcolor:#FFFF00]Sort by[/td][td=bgcolor:#FFFF00]Count[/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00]Fixed list[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9
[/td][td]H[/td][td]
7​
[/td][td][/td][td]Apple[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10
[/td][td]G[/td][td]
6​
[/td][td][/td][td]Orange[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11
[/td][td]F[/td][td]
4​
[/td][td][/td][td]Mango[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12
[/td][td]E[/td][td]
4​
[/td][td][/td][td]Lime[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13
[/td][td]A[/td][td]
4​
[/td][td][/td][td]Lemon[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14
[/td][td]D[/td][td]
3​
[/td][td][/td][td]Grape[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15
[/td][td]B[/td][td]
3​
[/td][td][/td][td]Cherry[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16
[/td][td]C[/td][td]
2​
[/td][td][/td][td]Banana[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17
[/td][td]I[/td][td]
2​
[/td][td][/td][td]Peach[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20
[/td][td]Lime[/td][td]Lime[/td][td]Orange[/td][td][/td][td]Grape[/td][td]Mango[/td][td][/td][td]Lemon[/td][td]Apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21
[/td][td]Peach[/td][td]Lemon[/td][td]Grape[/td][td][/td][td]Banana[/td][td]Orange[/td][td][/td][td]Apple[/td][td]Apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22
[/td][td]Cherry[/td][td]Apple[/td][td]Mango[/td][td][/td][td]Orange[/td][td]Grape[/td][td][/td][td]Banana[/td][td]Lemon[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23
[/td][td]Lime[/td][td]Mango[/td][td]Peach[/td][td][/td][td]Cherry[/td][td]Orange[/td][td][/td][td]Apple[/td][td]Apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24
[/td][td]Lime[/td][td]Cherry[/td][td]Apple[/td][td][/td][td]Mango[/td][td]Lemon[/td][td][/td][td]Orange[/td][td]Orange[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27
[/td][td]=INDEX($E$9:$E$17,MATCH(B2,$B$9:$B$17,0),1)[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
Upvote 0
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:

Code:
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?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top