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---
--
RyGuy
"Norman Jones" wrote:
> 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
>
>
> ---
> Regards.
> Norman
>
>
> "potentus" <(E-Mail Removed)> wrote in message
> news:352a4934-8aef-453c-955c-(E-Mail Removed)...
> > 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
>
>
Similar Threads
