Identify Missing Records
Posted by JAF on January 10, 2000 6:33 AM
Several times a month I have to compare the downloads of 2 system reports detailing accounts in the current month and the previous month. The reports are identical except that one report will have more records than the other, sometimes the current month will have more records than the previous month and vice versa.
There is no problem in getting the data into Excel, and I have been using a COUNTIF formula to compare the 2 sets of data. The problem with this method is that it takes AGES to calculate as the report can have anything upto around 20,000 records!!!
What I need is a QUICK (and preferably macro driven) way to identify the accounts which appear on the report with MORE records but not on the report with LESS records AS WELL AS the accounts which appear on the report with LESS records but are not on the report with MORE records (if that makes any sense!)
Any ideas, assistance etc appreciated.
Posted by Celia on January 12, 2000 2:19 AM
JAF
The following macro compares two single column ranges(checks "report1Range" with "report2Range") and colours yellow the cells in the first range that do not appear in the second range.
You can add further code to the macro, or write a separate one, similarly to compare "report2Range" with "report1Range".
Celia
Sub CompareColumns()
Dim dataValue As String
Dim response As Boolean
Dim dataFound As Boolean
Dim X As Integer
Dim I As Integer
'Select Report1 data
report1Range.Select
'Determine number of data cells
X = Selection.Cells.Count
'Loop through each data cell
For I = 1 To X Step 1
'Store data value
dataValue = Selection.Cells(I).Value
'Select Report2 data - search for data match
report2Range.Select
dataFound = DataSearch(dataValue)
'Return to Report1 data
report1Range.Select
'If no match, colour cell yellow
If Not (dataFound) Then
Selection.Cells(I).Interior.ColorIndex = 6
End If
Next I
End Sub
***********
Private Function DataSearch(iD) As Boolean
Dim result As Variant
'Search for data match and select cell if found
Set result = _
Selection.Find(What:=iD, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If result Is Nothing Then
DataSearch = False
Else
result.Activate
DataSearch = True
End If
End Function
