Need possible combinations of 7 unique characters.

[scottgorilla]

Ok I have seven unique text items in two columns and need a formula that will
give me all the possible combinations in a list. The columns are as follows.

Column 1
KEY
KNOB
2KEY
2KNOB
<blank>

Column 2
KEY
KNOB
KK
2KK
2KEY
2KNOB
<blank>

What I am looking for is a way to list in two columns the unique
combinations of Column1 & Column2 so I can insert a third column next to give
a value to be used in a VLOOKUP formula.

Thank You in advance

 

[Mike H]

Hi,

As a first stab at it try this. Right click your sheet tab, view code and
paste this in and run it


Sub serviant()
x = 1
lastrowA = Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrangeA = Range("A1:A" & lastrowA)
Set myrangeB = Range("B1:B" & LastrowB)
For Each c In myrangeA
For Each d In myrangeB
If c.Value <> d.Value Then
Cells(x, 3).Value = c
Cells(x, 4).Value = d
x = x + 1
End If
Next
Next
End Sub

Mike

 

[scottgorilla]

MikeH,
It worked.... why and how I have no idea but it worked and I surely do
appreciate it.

Thank You,

Scott

 

[Mike H]

glad I could help

MikeH,
It worked.... why and how I have no idea but it worked and I surely do
appreciate it.

Thank You,

Scott

 

[scottgorilla]

Mike,

Found one problem after further investigation and after putting it into my
wkst it does not give me duplicates side be side ie., knob...knob,key...key,
any suggestions???

Thanks,


Scott