Consolodating two tables.

[deeetour]

Hey guys,


Here's a little background, and what I am trying to do.


We import products that require testing at pre-determined quantity intervals, i.e. We need to test one in every 1000 items.

We raise a shipment for each import and a purchase order for each test.

I am pulling in the shipment table and purchase order table from SLQ via ODBC.

I would like to consolidate these tables to display a running history of imports and tests, per product, by date. I would like this to be automated as the source data would be updated in SQL almost daily.


Here's a sample sheet showing two dummy tables and my desired result - https://www.dropbox.com/s/04bkaw6wm98glc2/twotables.xlsx . I have no idea how to even begin tackling this, or if it's even possible.


Glad of any help you can provide!


Tom

 

[xld]

Tom,


Consolidating is simple enough ... as long as there is some key to tie the data together. Your tables seemed to be cnsolidated by sight, the two tables do not have any common values, so how do we know which goes with which?

 

[NARAYANK991]

Hi Tom ,


To add to what xld has already explained , you will have to do one of the following :


Have a Shipment field in Table2


or


Have a Purchase Order field in Table1


So either of the Shipment / Purchase Order fields will become the key to tie the two tables together.


Narayan

 

[xld]

This consolidates in date order

[pre]

Sub Consolidate()
Dim sh As Worksheet
Dim t1 As ListObject
Dim t2 As ListObject
Dim t3 As ListObject
Dim t1Rows As Long, t1Next As Long, t1StartRow As Long, t1StartCol As Long
Dim t2Rows As Long, t2Next As Long, t2StartRow As Long, t2StartCol As Long
Dim t3Next As Long

Set sh = Worksheets("Sheet1")
With sh
Set t1 = .ListObjects("Table1")
t1Rows = t1.DataBodyRange.Rows.Count
t1StartRow = t1.HeaderRowRange.Row + 1
t1StartCol = t1.HeaderRowRange.Column

Set t2 = .ListObjects("Table2")
t2Rows = t2.DataBodyRange.Rows.Count
t2StartRow = t2.HeaderRowRange.Row + 1
t2StartCol = t2.HeaderRowRange.Column
End With

With Worksheets("Sheet2")

.Range("A1:H1").Value = Array("Date", "Type", "Column1", "Part 1 Ship", "Part 1 PO", ".", "Part 2 Ship", "Part 2 PO")
t3Next = 1

For t1Next = t1StartRow To t1StartRow + t1Rows - 1

t3Next = t3Next + 1
.Cells(t3Next, "A").Value = sh.Cells(t1Next, t1StartCol + 1).Value
.Cells(t3Next, "B").Value = "Shipment"
.Cells(t3Next, "C").Value = sh.Cells(t1Next, t1StartCol).Value
If sh.Cells(t1Next, t1StartCol + 2).Value = 1 Then

.Cells(t3Next, "D").Value = sh.Cells(t1Next, t1StartCol + 3).Value
Else

.Cells(t3Next, "G").Value = sh.Cells(t1Next, t1StartCol + 3).Value
End If
Next t1Next

For t2Next = t2StartRow To t2StartRow + t1Rows - 1

t3Next = t3Next + 1
.Cells(t3Next, "A").Value = sh.Cells(t2Next, t2StartCol + 1).Value
.Cells(t3Next, "B").Value = "Purchase Order"
.Cells(t3Next, "C").Value = sh.Cells(t2Next, t2StartCol).Value
If sh.Cells(t2Next, t2StartCol + 2).Value = 1 Then

.Cells(t3Next, "E").Value = sh.Cells(t2Next, t2StartCol + 3).Value
Else

.Cells(t3Next, "H").Value = sh.Cells(t2Next, t2StartCol + 3).Value
End If
Next t2Next

.UsedRange.Sort key1:=.Range("A1"), order1:=xlAscending, Header:=xlYes

.ListObjects.Add(xlSrcRange, Range("$A$1:$H$1").Resize(t1.DataBodyRange.Rows.Count + _
t2.DataBodyRange.Rows.Count + 1), , _
xlYes).Name = "tblConsolidated"
.ListObjects("tblConsolidated").TableStyle = "TableStyleMedium2"
End With
End Sub

[/pre]