Cross product of data in multiple rows and columns

**Pavan Renjal** · 02-12-2014, 04:56 AM

I have 2 sets of data. One which is variable and the other which will be constant. I need to get a cross product of these 2 data. Please help me in achieving this. I have attached the sheet with data in "Customer" and "Products" tab and the desired result set in "Result Tab".

**Izandol** · 02-12-2014, 05:05 AM

This is Cartesian product:

Sub Cartesian()
   Dim cn                          As Object
   Dim rs                          As Object
   Dim sQuery                      As String
   Dim i                           As Long
   
   Const adOpenStatic = 3
   Const adLockReadOnly = 1
   Const adCmdText = 1
   
   sQuery = "SELECT * FROM [Customer$], [Products$]"
   
   Set cn = New ADODB.Connection
   
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
                          "Extended Properties=""Excel 12.0 Macro;HDR=YES"""
      .Open
   End With
   
   Set rs = CreateObject("ADODB.Recordset")
   
   rs.Open sQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
   
   With rs

      If Not .EOF Then
         
         For i = 1 To .Fields.Count
            Sheets("Result").Cells(1, i).Value = .Fields(i - 1).Name
         Next i
         
         Sheets("Result").Cells(2, 1).CopyFromRecordset rs

      End If
      .Close

   End With
   cn.Close
End Sub

**Pavan Renjal** · 02-12-2014, 06:07 AM

@Izandol: Thanks a ton! This is exactly what i needed! However, i am facing a small issue here. I missed out a column in "Customer" tab which also contains a date field. In the "Result" tab the date format is not retained. I need to change the format to date each time in the result set. Please let me know if this can be fixed?

**Izandol** · 02-12-2014, 06:47 AM

Which column will have date field in result? You may simply add formatting at end of routine:

Sheets("Result").Range("E:E").Numberformat = "dd-mmm-yyyy"

for example.

**Pavan Renjal** · 02-12-2014, 06:55 AM

Originally Posted by Izandol

Which column will have date field in result? You may simply add formatting at end of routine:

Sheets("Result").Range("E:E").Numberformat = "dd-mmm-yyyy"

for example.

Thanks! Its working fine now. I modified the column range and date format accordingly

**apo** · 02-12-2014, 07:50 AM

Hi..

I am a little slow.. but here is maybe an alternative..

In the attachment: Press the "Click!" button to see the results on "Sheet1"

Private Sub CommandButton1_Click()
    Dim rng1 As Range, i As Long
    Set rng1 = Sheets("Products").Range("A1").CurrentRegion.Offset(1).Resize(Sheets("Products").UsedRange.Rows.Count - 1)
    With Sheets("Sheet1")
        .Range("A1").Resize(1, 2).Value = Sheets("Customer").Range("A1:B1").Value
        .Range("C1").Resize(1, 4).Value = Sheets("Products").Range("A1:D1").Value
        .Columns.AutoFit
        For i = 2 To Sheets("Customer").Range("A" & Rows.Count).End(xlUp).Row
            .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row).Offset(1).Resize(Sheets("Products").UsedRange.Rows.Count - 1, 2).Value _
                    = Array(Cells(i, 1), Cells(i, 2))
            .Range("C" & .Range("C" & Rows.Count).End(xlUp).Row).Offset(1).Resize(rng1.Rows.Count, 4).Value = rng1.Value
        Next i
    End With
End Sub