Compare 2 excel workbooks

[potentus]

Hi,

I have 2 excel sheets generated daily :
Daily a
Daily b

I need to compare column A in "Daily a" to Column a in "Daily b" and
show all items that dont appear in both (deltas)
So for example the number 1 only appears in "Daily a" it would be
listed, but if it was in both daily spreadsheets it would not


Thanks


Neo

 

[Norman Jones]

Hi Neo,

Try the Compare add-in, created by
Myrna Larson and Bill Manville, which
can be downloaded from the
Downloads page on Chip Pearson's site:

http://www.cpearson.com/Excel/download.htm

 

[ryguy7272]

Use this:
Sub MatchedAandB()
On Error GoTo errHandler
Dim iRowNewProjects As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim wks As Worksheet
Dim colExistingB As New Collection
Dim sTempProjectNumber As String
Dim bFoundDuplicate As Boolean
Set wks = Application.ActiveSheet

iCol = 2
iRow = 2
'Fill collection with check values
Do Until wks.Cells(iRow, iCol).Value = ""
colExistingB.Add wks.Cells(iRow, iCol).Value, CStr(wks.Cells(iRow,
iCol).Value)
iRow = iRow + 1
Loop
'Now run down the column to be checked ('A')
iCol = 1
iRow = 2
iRowNewProjects = iRow
Do Until wks.Cells(iRow, iCol).Value = ""
sTempProjectNumber = wks.Cells(iRow, iCol).Value
For i = 1 To colExistingB.count
If sTempProjectNumber = colExistingB(i) Then
bFoundDuplicate = True
Exit For
End If
Next i
If bFoundDuplicate = False Then
wks.Cells(iRowNewProjects, iCol + 2).Value = sTempProjectNumber
iRowNewProjects = iRowNewProjects + 1
End If
bFoundDuplicate = False
iRow = iRow + 1
Loop
exitHere:
Exit Sub
errHandler:
If Err.Number = 457 Then
'Already in collection
Resume Next
Else
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation
Resume exitHere
End If
End Sub


I didn't develop it; just found it on this DG a while back.

Regards,
Ryan---