1

I have to produce a list of combinations which are indicators to fields as I am trying to produce some conditions to interrogate data.I have some code for the combinations from here.

In the example of Power/Torque/Cylinders with a combination of 1,2,4:

Fields for combinations

I need to work out the combinations of those 3 fields, so the output would be:

Example combinations within combination

So essentially all combinations but not from the same 'bucket' if that makes sense?

Edit: The number of combinations (ie 3 in the example) will change as per the link I provided. The combinations from the link determine which field to look at or use. Eg combination 123 would be the first 3 fields in the image. Combination 1,2 would be tge first 2 and 1,3 would be first and last. I have the code for that.

Now we have the combination buckets, need to work through the combinations across thise fields.

Also I am looking for guidance on how to approach the algo and not necessarily someone to do it for me 😊

As another example, if 1,2,3 were the column combination, the expected output would be:

20-0.5-200

20-0.5-300

20-0.5-400

etc

Improve this question
6
  • 1
    What are you having trouble with? You need to ask a specific programming question, not just post a list of requirements and expect somebody to write it for you. Show us what you already have. Commented Mar 12, 2018 at 20:46
  • Looks like you simply need nested For loops, and columns being addressed as integers. (Use Range.Cells -msdn.microsoft.com/en-us/vba/excel-vba/articles/…) Commented Mar 12, 2018 at 20:50
  • So you want all combinations of a number of set of cells, correct? Will it always be 3 sets? Commented Mar 12, 2018 at 20:54
  • You want the user to be able to select what columns are used "on-the-fly" dynamic # of rows and dynamic # of columns? Commented Mar 12, 2018 at 21:00
  • Have edited. Hopfully provides more context. Commented Mar 12, 2018 at 22:21

3 Answers 3

Reset to default
2

Nested Loop:

Sub allCombo(set1 As Range, set2 As Range, set3 As Range)
    Dim c1, c2, c3, n
    For Each c1 In set1
        For Each c2 In set2
            For Each c3 In set3
                n = n + 1
                Debug.Print "#" & n, c1, c2, c3
            Next c3
        Next c2
    Next c1
End Sub

Example Usage:

Sub test()
    allCombo [I2:I4], [J2:J3], [L2:L3]
End Sub

Result:

#1             20            0.5           4 
#2             20            0.5           8 
#3             20            0.8           4 
#4             20            0.8           8 
#5             30            0.5           4 
#6             30            0.5           8 
#7             30            0.8           4 
#8             30            0.8           8 
#9             40            0.5           4 
#10            40            0.5           8 
#11            40            0.8           4 
#12            40            0.8           8
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @ashleedawg but as mentioned above tge combinations could be 1 to n and not necessarily 3. Steve
1

Here is an option that is completely dynamic: 

Option Explicit

Sub MakeCombos()

Dim myCols As Variant, i As Long, j As Long, myCombos() As Variant
Dim temp() As Variant, LastRow As Long, lngCol As Long, myLens() As Long
Dim index() As Long, totalCombs As Long, count As Long

    '' Prompt user for columns N.B. there is no
    '' data validation, so enter with caution
    myCols = Split(InputBox("Enter the columns as a comma separated list: ", "Column Combos 3000"), ",")
    ReDim myCombos(0 To UBound(myCols))
    ReDim index(0 To UBound(myCols))
    ReDim myLens(0 To UBound(myCols))
    totalCombs = 1

    '' This loop is simply populating myCombos
    '' with the chosen columns. We are also populating
    '' myLens with the maximum length of each column
    For i = 0 To UBound(myCols)
        lngCol = CLng(myCols(i))
        With ActiveSheet
            LastRow = .Cells(.Rows.count, lngCol).End(xlUp).Row
        End With

        ReDim temp(0 To LastRow - 2)

        For j = 2 To LastRow
            temp(j - 2) = Cells(j, lngCol)
        Next j

        myCombos(i) = temp
        myLens(i) = LastRow - 2

        '' Get the total number of combinations
        totalCombs = totalCombs * (LastRow - 1)
    Next i

    '' This is where the magic happens. Note, we
    '' don't have nested for loops. Rather, we are keeping
    '' up with the correct index with the appropriately
    '' named array "index". When one of the indices exceeds
    '' the maximum length, we reset that index and increment
    '' the next index until we have enumerated every combo
    While (count < totalCombs)
        For j = 0 To UBound(myCols)
            Cells(count + 20, j + 1) = myCombos(j)(index(j))
        Next j

        j = UBound(index)
        index(j) = index(j) + 1

        Do While index(j) > myLens(j)
            index(j) = 0
            j = j - 1
            If j < 0 Then Exit Do
            index(j) = index(j) + 1
        Loop

        count = count + 1
    Wend

End Sub

Here is the example input:

enter image description here

And here is the top of the output for entering 1,2,4 at the prompt:

enter image description here

And here is the top of the output for entering 2,3 at the prompt:

enter image description here

Improve this answer

4 Comments

I'll second that comment. I was thinking tgere was no way without recursion. Very clever. Thanks vm for your effort 😊
@SteveP495, glad I could help! I just wanted to let you know that generally speaking, you can transform a recursion algorithm into some sort of loop structure algorithm. Here is a post from https://softwareengineering.stackexchange.com/ that has more information about the specific differences.
Re: "don' t have nested for loops" ... not sur why that's relecant, but just to point out: nested while/for loops are still loops
@ashleedawg, my point in saying that is that if one sees a solution with nested for loops, it is a clear indication that it is static. It is precisely what the OP was NOT asking for. Any nested for loop solution that will get you n columns of combinations, will have n nested for loops. In order to get any other number, say m, of combinations, you will have to completely refactor your code to have m nested for loops. So you end up writing many hard coded subroutines that will inevitably limit the user. What if the user had 20 columns? 40 columns? The solution above is good as is.
1

Here is a sub that first determines the number of items in columns I, J, L and adjust the loops accordingly:

Sub SteveP()
    Dim N1 As Long, N2 As Long, N3 As Long, K As Long
    Dim m1 As Long, m2 As Long, m3 As Long
    Dim a As Variant, b As Variant, c As Variant

    N1 = Cells(Rows.Count, "I").End(xlUp).Row
    N2 = Cells(Rows.Count, "J").End(xlUp).Row
    N3 = Cells(Rows.Count, "L").End(xlUp).Row
    K = 1

    For m1 = 2 To N1
        a = Cells(m1, "I")
        For m2 = 2 To N2
            b = Cells(m2, "J")
            For m3 = 2 To N3
                c = Cells(m3, "L")
                Cells(K, "M") = a
                Cells(K, "N") = b
                Cells(K, "O") = c
                K = K + 1
            Next m3
        Next m2
    Next m1
End Sub

enter image description here

Improve this answer

4 Comments

many thanks but the issue is that the number of fields could b 1-n. EG could be one field, two fields, three fields or 4. There are only 4 fields in my example data set therefore 4 would be the max of course.
I'll need to go recursive no doubt but am not sure how to approach as the number of =values in each column (field) is not uniform (necessarily) if that makes sense ?
@SteveP495 Be aware that in this simple approach ** the number of loops depends on the number of columns ** ....................if we change the number of columns, the code will reqire modification.
yes, thanks understood. The issue is that the # of columns can change so I think recursion is the only way to get this done..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.