BOM Compare - compare between tow versions of data

[dispro]

Hello,
I struggled with this problem for a few days and would like to get some help.
I need to compare two versions of the same table when the differences are displayed in a new table.

Table 1 (old version) - sheet1:



PartNum
Location


CCN01905
J6


CCN01905
J100


CCN01905
J200


CCN01905
J300


CCN01905
J400


CCN04455
J800


CCN05363
J3


CCP01960
C1


CCP01960
C3


CCP01960
C5


CCP01960
C143


CCP01960
C147


CCP02583
C19


CCP02583
C20



Table 2 (new version) - sheet2:



PartNum
Location


CCN01905
J6


CCN01905
J201


CCN01905
J300


CCN01905
J400


CCN04455
J800


CCN05363
J3


CCP01960
C1


CCP01960
C3


CCP01960
C5


CCP01960
C143


CCP01960
C147


CCP02581
C19


CCP02581
C20




The table of differences should be created in sheet 3 and should include the following fields:



PartNum (Old)
PartNum (New)
Loacation (Old)
Loaction (New)




Additional table (in sheet3) should include a number of times each PartNum appears in both versions.



PartNum
Old Version
New Version


CCN01905
5
4




1. As you can see each Partnum can appear more then once in the table but in different locations.
2. The same PartNum (with the same location) could appear in different rows in both versions but it's don't mean that there was a difference between them.

Thanks!

[westconn1]

1. if there are multiple instances of a part number in each table, how can you compare locations for each table?

how to handle part numbers in either version, but not in the other?

how many rows (approx) of data in each table?

post a workbook with some sample data for testing

[westconn1]

you can try like this, see if it works for what you want, or what changes you require


Dim cn As Connection, rs As Recordset, rs2 As Recordset, cel As Range

Set cn = New Connection
Set rs = New Recordset
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With
Sheets("sheet3").Range("a1:g1") = Array("PartNum", "Old Location", "New Location", , "Count Old", "Count New")

Sql = "select partnum from ['old ver$'] union select partnum from ['new ver$']"
rs.Open Sql, cn, adOpenStatic, adLockReadOnly
Sheets("sheet3").Range("a2").CopyFromRecordset rs
rs.MoveLast
lastrec = rs(0)
rs.Close
Set rs2 = New Recordset
For Each cel In Sheets("sheet3").Range("a:a")
If Not IsEmpty(cel) Then
Sql = "select distinct location from ['old ver$'] where partnum = '" & cel.Value & "'"
rs.Open Sql, cn, adOpenStatic, adLockReadOnly
Sql = "select distinct location from ['new ver$'] where partnum = '" & cel.Value & "'"
rs2.Open Sql, cn, adOpenStatic, adLockReadOnly
reccnt = rs.RecordCount
If rs2.RecordCount > reccnt Then reccnt = rs2.RecordCount
If reccnt > 1 Then cel.Offset(1).Resize(reccnt - 1).Insert xlShiftDown
cel.Offset(, 1).CopyFromRecordset rs
cel.Offset(, 2).CopyFromRecordset rs2
rs.Close
rs2.Close
Sql = "select count(partnum) from ['old ver$'] where partnum = '" & cel.Value & "'"
rs.Open Sql, cn, adOpenStatic, adLockReadOnly
cel.Offset(, 5).Value = rs(0)
rs.Close
Sql = "select count(partnum) from ['new ver$'] where partnum = '" & cel.Value & "'"
rs.Open Sql, cn, adOpenStatic, adLockReadOnly
cel.Offset(, 6).Value = rs(0)
rs.Close


End If
If cel = lastrec Then Exit For
Next
cn.Closethis will require a reference to ADO or ACE as appropriate to your versions