Distribute known numbers into columns using VBA

[Luthius]

I have 2 set of numbers that I would like to mix them.
My first set contains 15 numbers that I would like to combine with a set of 10 numbers and distribute them into colunms.
From the set of 15 numbers, the code will choose 9 numbers (randomly) and combine with 5 numbers from the set of 10 numbers (randomly). It will generate a new set of 15 numbers (9 from my first set combined with 05 of the second set of numbers).


I know that it will generate a huge quantity of group of 15 numbers combined, but I would like to delimite in the code, by input box or something.

1st Set with 15 unique Numbers (Range B2:P2)
2nd Set with 09 unique Numbers (Range B3:k3)

Distribution will occur starting on row number 5 and distributed from Column A to Column O.

Luthius

 

[lrobbo314]

Re: How to distribute known numbers into columns using VBA

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[Luthius]

Re: How to distribute known numbers into columns using VBA

Your code is amazing, but it takes a while to run when there are too many groups to be generated.

I would like to share with you a code and at the same time request assistance on how to make some changes in this code to try merge it with your solution.

The code I found on https://www.get-digital-help.com/201...-combinations/, but instead an UDF I would like to make as was done before.

[COLOR=#333333]Public result() As Variant[/COLOR]Function Combinations(rng As Range, n As Single)

rng1 = rng.Value

ReDim result(n - 1, 0)
    
Call Recursive(rng1, n, 1, 0)

ReDim Preserve result(UBound(result, 1), UBound(result, 2) - 1)
Combinations = Application.Transpose(result)
End Function


Function Recursive(r As Variant, c As Single, d As Single, e As Single)
Dim f As Single

For f = d To UBound(r, 1)
    result(e, UBound(result, 2)) = r(f, 1)
    If e = (c - 1) Then
        ReDim Preserve result(UBound(result, 1), UBound(result, 2) + 1)  
        For g = 0 To UBound(result, 1)
            result(g, UBound(result, 2)) = result(g, UBound(result, 2) - 1)
        Next g
    Else
        Call Recursive(r, c, f + 1, e + 1)
    End If    
Next f [COLOR=#333333]End Function[/COLOR]