Basic user needing help with a formula to return combinations

[gajt73]

Hi Everyone,

Forgive me in advance for posting for what will be for many of you a basic question, so I thank you in advance for your help and understanding.

I have a spreadsheet, that has 4 columns of numbers and I want to return every combination of the those numbers available in a new column.

I did get some assistance from another user but it isn't right the results returned aren't right. I am trying to return every combination for these columns. In column 1 there is 8 numbers, in column 2 there is 6 numbers, in column 3 there is 6 numbers, and in column 4 there is 6 numbers - giving me 1728 combinations. I can't seem to see where I'd attach the spreadsheet to show my problem. Help someone please??

Thank you in advance.

Regards
Grant

 

[Michael M]

Try this VBA, previously posted by Santosh on StackOverflow
I think it would be a better option than formulas !!

Sub combinations()

    Dim c1() As Variant
    Dim c2() As Variant
    Dim c3() As Variant
    Dim c4() As Variant

    Dim out() As Variant
    Dim j As Long, k As Long, l As Long, m As Long, n As Long


    Dim col1 As Range
    Dim col2 As Range
    Dim col3 As Range
    Dim col4 As Range

    Dim out1 As Range


    Set col1 = Range("A1", Range("A1").End(xlDown))
    Set col2 = Range("B1", Range("B1").End(xlDown))
    Set col3 = Range("C1", Range("C1").End(xlDown))
    Set col4 = Range("D1", Range("D1").End(xlDown))

    c1 = col1
    c2 = col2
    c3 = col3
    c4 = col4

    Set out1 = Range("G2", Range("K2").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c4)))
    out = out1

    j = 1
    k = 1
    l = 1
    m = 1
    n = 1


    Do While j <= UBound(c1)
        Do While k <= UBound(c2)
            Do While l <= UBound(c3)
                Do While m <= UBound(c4)
                    out(n, 1) = c1(j, 1)
                    out(n, 2) = c2(k, 1)
                    out(n, 3) = c3(l, 1)
                    out(n, 4) = c4(m, 1)
                    n = n + 1
                    m = m + 1
                Loop
                m = 1
                l = l + 1
            Loop
            l = 1
            k = k + 1
        Loop
        k = 1
        j = j + 1
    Loop


    out1.Value = out
End Sub

 

[gajt73]

Thank yo so much Michael M, I really appreciate the response. I'll give it a go. All the best