Creating a cartesian product in Excel with a Visual Basic macro

The term cartesian product sounds flash doesn't it? Like some sort of Star Trek thingy wossit.... When I first asked my "goto excel guy" how I created one, I didn't know that what I was asking for had a specific, flashy name.

So what is a cartesian product?

You probably already knows what a cartesian product is, even if you don't know that that is what it is called. In essence a cartesian product is the result set of all possible ordered pairs of data. Although normally used in mathematics, it is a process that can also be applied to non-numerical data sets like table fields (in SQL) or ranges (in Excel): If you want to create a result set of all possible combinations of two tables then you want a cartesian product!

So how do I create a cartesian product in Excel?

You need a marco that runs through your two ranges and gives you a new range of all the possible combinations. Simple right? The problem is there isn't a cartesian product macro that works out of the box and even common excel plugins like ASAP Utilities don't have one either.

Visual Basic to the rescue! Here is how you make a cartesian product macro in Excel 2007:


Firstly open your Developer tab and click Visual Basic to open the Visual Basic Editor; you should see something like this:



You need to create a Module in your wookbork, so right-click on the Modules folder and choose Insert > Module:



Copy the following code into the right-hand side of the Visual Basic Editor (big thanks to Andrew Edge for his masterful VB skills):

Sub cartesianproduct()
Dim startrange As Range

range1 = Application.InputBox(Prompt:="Please Select First Range", Type:=8)
range2 = Application.InputBox(Prompt:="Please Select Second Range", Type:=8)
Set startrange = Application.InputBox(Prompt:="Please select where you want to put it", Type:=8)

array1 = [range1]
array2 = [range2]

startrange.Select
For i = 1 To UBound(array1)
    For x = 1 To UBound(array2)
        z = z + 1
        ActiveCell.Offset(z, 0).Value = array1(i, 1)
        ActiveCell.Offset(z, 1).Value = array2(x, 1)
    Next
Next
End Sub


You will see that your macro now has a name "cartesianproduct":



Close the Visual Basic Editor and return to Excel. All that is needed now is to have two ranges of data. Start your macro by visiting the Developr tab and then click Macros. You'll see your cartesianproduct macro in a list.

Select cartesianproduct and click Run. You'll be asked to select two ranges and a cell where you want the resultset to be placed (it's usually better to put the resultset on a seperate worksheet)

Sit back and wait for the macro to run.... That's it!

Caution!

Due to the nature of cartesian products and the limitations of the number of rows excel will support, this macro is only useful for relatively small range pairs. You have been warned!

0 comments:

Post a Comment

 

tootricky's blog 2010