Combination macro

[k.03a]

Hi everyone, I'm looking for a combination macro and can't find what I need. Unfortunately I'm also a VBA newbie.

The simplified version goes like this: I have a list of items in rows A and B
A B
x x
y y
z z

And I want the list of all combinations in column C
C
x-x
x-y
x-z
y-x
etc...

the problem is the list of items is unknown in length, and contains about 150 different items.


The full version of the problem is a bit more complex because I want to avoid repetitions. For example

A B
x x
x x
z z

would return
C
x-x
x-z
z-x
z-z

Can anyone help?

Thanks a lot !

 

[id107]

Not a pretty solution, but:

Sub Combination()
 
    Dim lr As Long, lr2 As Long, i As Long, n As Long, cRow As Long
 
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lr2 = Range("B" & Rows.Count).End(xlUp).Row
    cRow = 2
 
    For i = 2 To lr
        For n = 2 To lr2
            If Application.WorksheetFunction.CountIf(Range("C2:C" & cRow), Range("A" & i).Value & "-" & Range("B" & n).Value) = 0 Then
                Range("C" & cRow).Value = Range("A" & i).Value & "-" & Range("B" & n).Value
                cRow = cRow + 1
            End If
        Next n
    Next i
 
End Sub

 

[k.03a]

Works perfectly. Thanks !