How to do a cartesian join in Excel?

[rkg]

I have two worksheets, each having the primary key values stored i
column A. I want to create a new worksheet wherein each value in Colum
A of worksheet 1 should join against each value in Column A of workshee
2 and be stored in column A and B of the new worksheet.

Therefore, if Worksheet 1 and 2 look like:
Wk1-Col-A Wk2-Col-A
100 23
101 45
60
Wk3-Col-A-and-B should look like:
100 23
100 45
100 60
101 23
101 45
101 60

Any idea how to do this using macros or any other worksheet function?

Thanks so much for your help

 

[Debra Dalgleish]

You could create the list with programming. For example:

'==============================
Sub CopyCartersian()

Dim ws3 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim lRow1 As Long
Dim lRow2 As Long
Dim lPaste As Long
Dim lCount As Long

Set ws3 = Worksheets("Sheet3")
Set rng1 = Worksheets("Sheet1").Range("A1").CurrentRegion
Set rng2 = Worksheets("Sheet2").Range("A1").CurrentRegion
lRow1 = rng1.Rows.Count
lRow2 = rng2.Rows.Count

For lCount = 1 To lRow2
lPaste = lRow1 * (lCount - 1) + 1
rng1.Copy Destination:=ws3.Cells(lPaste, 1)
With ws3
.Range(.Cells(lPaste, 2), .Cells(lPaste + lRow1 - 1, 2)) _
.Value = rng2.Cells(lCount).Value
End With
Next lCount

End Sub
'==========================