Help showing all permutations of values in two ranges


Posted by Joe Clement on February 12, 2002 4:02 PM

I need a program that will take the values of two columns of text, col A and col b, and produce a third column that has all the combinations of the words in the two columns.

Ex:
Col A: painting, drawing, sculpture
Col B: class, school
Col C: painting class, drawing class, sculpture class, painting school, drawing school, sculpture school.

I've achieved this so far by making col A in a column and Col B across one row (forming a matrix), and then using the concatenate command. But I'm trying to figure out a way of doing it that would put it all into one column. I'm thinking there must be a way with perhaps cse formulas?

Please help, thank you so much,

Joe



Posted by Juan Pablo G. on February 12, 2002 7:23 PM

Try with this UDF. It must be entered with Control Shift Enter being an Array Formula.

Function MyPerm(Rng1 As Range, Rng2 As Range) As Variant
Dim T As Variant
Dim Cll1 As Range
Dim Cll2 As Range
Dim i As Integer

ReDim T(1 To Rng1.Count * Rng2.Count, 1 To 1)
For Each Cll1 In Rng1
For Each Cll2 In Rng2
i = i + 1
T(i, 1) = Cll1 & " " & Cll2
Next Cll2
Next Cll1
MyPerm = T
End Function

Rng1 would be A1:A3 and Rng2 would be B1:B2 in your example.

I entered this

{=MyPerm(A1:A3,B1:B2)}

which equals to

{"Painting Class";"Painting School";"Drawing Class";"Drawing School";"Sculpture Class";"Sculpture School"}

Juan Pablo G.