Finding possible combinations

[Niels1994]

Hello all,

I'm having this problem with our company's products and what combinations are possible.
The following is the situation:

[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD]CUSTOMER_ID[/TD]
[TD]PRODUCT_ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]

I have specified for each customer what products they have bought. And now I want to generate all possible product-combinations for each customer.

So, my output would be like this:
[TABLE="class: grid, width: 1"]
<tbody>[TR]
[TD]CUSTOMER_ID[/TD]
[TD]POSSIBLE_COMBINATIONS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A-B, A-C, A-D, B-C, B-D, C-D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B-C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A-B, A-E, A-F, A-G, B-E, B-F, B-G, E-F, E-G, F-G[/TD]
[/TR]
</tbody>[/TABLE]

How can I do this in Excel?
Can this be done by using regular formulas/functions, or do I need VBA for this? (in case I need VBA, any suggestions how to start?)


Thank you very much!
~ Niels

 

[Niels1994]

I found a solution that solves it more or less.

Sub Combinations()


    Dim customerID As Integer
    Dim combinatiom As String
    Dim i, j As Integer
    Dim r1 As Range
    Dim sameProduct, sameCustomer As Boolean
    
    sameProduct = True
    i = 2
    For Each r1 In Range("A2", "A25")
        sameCustomer = True
                j = 1
                Do While sameCustomer
                    combination = ""
                    If r1.Offset(j, 0) = r1.Value Then
                        combination = r1.Offset(0, 1).Value + "-" + r1.Offset(j, 1).Value
                        j = j + 1
                        r1.Offset(0, i).Value = combination
                        i = i + 1
                    Else
                        sameCustomer = False
                        i = 2
                    End If
                Loop
    Next r1
    
End Sub