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.
