vba code generate array.

[montecarlo2012]

Generate a VBA code that generate 20 rows of length 5 from a set of 12 numbers, that meet the specific frequency of each number, no permutations.

DISPLAY AT F2:J21

frequency for	1​	equal	8​
frequency for	2​	equal	7​
frequency for	3​	equal	9​
frequency for	4​	equal	10​
frequency for	5​	equal	11​
frequency for	6​	equal	10​
frequency for	7​	equal	11​
frequency for	8​	equal	7​
frequency for	9​	equal	6​
frequency for	10​	equal	5​
frequency for	11​	equal	7​
frequency for	12​	equal	9​

Sub GenerateCombinations()
    Dim numbers(1 To 12) As Integer
    Dim frequencies(1 To 12) As Integer
    Dim combinations(1 To 20, 1 To 5) As Integer
    Dim i As Integer, j As Integer, k As Integer, n As Integer, count As Integer
    Dim temp As Integer
    
    ' Set the numbers and their frequencies
    numbers(1) = 1
    numbers(2) = 2
    numbers(3) = 3
    numbers(4) = 4
    numbers(5) = 5
    numbers(6) = 6
    numbers(7) = 7
    numbers(8) = 8
    numbers(9) = 9
    numbers(10) = 10
    numbers(11) = 11
    numbers(12) = 12
    
    frequencies(1) = 8
    frequencies(2) = 7
    frequencies(3) = 9
    frequencies(4) = 10
    frequencies(5) = 11
    frequencies(6) = 10
    frequencies(7) = 11
    frequencies(8) = 7
    frequencies(9) = 6
    frequencies(10) = 5
    frequencies(11) = 7
    frequencies(12) = 9
    
    ' Generate combinations
    For i = 1 To 20
        ' Reset count for each combination
        count = 0
        
        ' Generate a combination of 5 unique numbers
        Do While count < 5
            n = Int((12 * Rnd) + 1) ' Generate a random number between 1 and 12
            If frequencies(n) > 0 Then ' Check if frequency of the number is not exceeded
                combinations(i, count + 1) = numbers(n) ' Add the number to the combination
                frequencies(n) = frequencies(n) - 1 ' Decrease the frequency of the number
                count = count + 1 ' Increase the count of numbers in the combination
            End If
        Loop
        
        ' Reset frequencies for the next combination
        For j = 1 To 12
            frequencies(j) = frequencies(j) + 1
        Next j
    Next i
    
    ' Sort the combinations
    For i = 1 To 19
        For j = i + 1 To 20
            For k = 1 To 4
                If combinations(i, k) > combinations(j, k) Then
                    For n = 1 To 5
                        temp = combinations(i, n)
                        combinations(i, n) = combinations(j, n)
                        combinations(j, n) = temp
                    Next n
                ElseIf combinations(i, k) = combinations(j, k) And combinations(i, k + 1) > combinations(j, k + 1) Then
                    For n = 1 To 5
                        temp = combinations(i, n)
                        combinations(i, n) = combinations(j, n)
                        combinations(j, n) = temp
                    Next n
                End If
            Next k
        Next j
    Next i
    
    ' Display the combinations
    Range("F2:J21") = combinations
    
End Sub

this code generate repetition in the rows or permutations, and the frequency is wrong also,
please somebody can check where is the problem.
thank you for reading this

 

[PeteWright]

With your arrays starting at '1', do you have 'Option Base 1' set in your code?
I have not yet checked your code, but before I do so, erm ... do you use some (mathematical) equations or formulas to calculate your numbers? If yes, could you post them here please.

 

[montecarlo2012]

PeteWright Thanks for reading my post.
It is just basic math here.
I just want to control the frequency of the numbers
more or less is about generate 20 lines of numbers or rows in 5 columns so the basic math here is you have 20 times 5 is 100 cells
that will be fill out with the numbers but with the frequency.
can be any number, the main idea here is the location
just a different example in order to illustrated better the idea

here you have 10 numbers in 7 lines taken 5 at the time
I will give you another example with 10 numbers but this time with 6 lines

thank you so much for your concern.

 

[PeteWright]

Okay, we're getting closer ...
Now there is one last thing I would like to ask you: What are the conditions of the generated numbers (eg. numbers from 1 to 12, max 3 repetitions, etc.)
Could you show an example of the final result as a worksheet? You could fill your cells manually with values, and post a screenshot of that.

 

[montecarlo2012]

Ok, I think I have to illustrated better in order to avoid different interpretation like max 3 repetitions, one of the screenshot have just 3 as frequency yes, but it is just that example, because if you see
the example before every number have different freq. so the key here or the main condition is to generate the amount of lines but counting that the numbers are in the correct frequency, if you don't mind I am able to upload more screenshot, but for now I give you my excel spreadsheet I hope to get it. thanks again.

sorry here is 6 lines. on D2

 

[PeteWright]

View attachment 87353

The arrangement of the numbers on the right remembers me of Pascal's Triangle
I think I got the point. Let me do some VBA magic meanwhile ...

 

[montecarlo2012]

Thanks. as a little comment there are not to much math here, is more about programming skills. nested loops,

you can see here for example I used

here you can realize on location 1 is the number 27 on second 28 etc.
and each number shows up with the frequency expected.

 

[montecarlo2012]

in the spreadsheet looks like

that would be magic
thank you

 

[PeteWright]

Uh, I was just about to code something, but I see the numbers changed.

So now I have to ask you again.
How should the numbers be generated? With VBA or user input?
What is the range of possible numbers?

 

[montecarlo2012]

Numbers or column B to be sure we are talking about the same idea, yes I pick the number or in your terms "user input"
range of possible numbers, you mean how many numbers I would like to pick?
I am thinking about max 31