Ask a question »

Excel - Compare columns to find missing data

December 2015

Microsoft Excel Office software has a range of features that enable the user to create worksheets according to their needs and requirements. It is easy to create a macro to compare columns to find missing data in Excel. Macros in Excel can be used to do calculations and data manipulations in the worksheets. It is not required to write Excel macros from scratch as there are many resources available on the web that provide code for macros. Knowledge of programming may prove helpful but is not essential to create a macro to compare columns to find missing data in Excel Worksheets.

Excel - Compare columns to find missing data


I need help please! I have two worksheets (C & Q). Each worksheet contains four columns, each column has different information (A=Date, B=Misc, C=Description, D=Amount), but both worksheets are alike. I am trying to compare Column D in both worksheets in order to find missing data in Worksheet Q.


If the macro finds the missing data, what do you want to do with it? In my macro, the data in column D of sheet C which is missing in the same column of sheet Q will be marked yellow. This may or may not be what you want to do with the missing data. Here is the macro below. Modify it with whatever you want.
First run the macro and look at sheet C to see what happens before modifying the macro:

Sub test()    
Dim cfindq As Range, rc As Range, cc As Range, x As Double    
On Error Resume Next    

With Worksheets("c")    
.Cells.Interior.ColorIndex = xlNone    
Set rc = Range(.Range("d2"), .Range("d2").End(xlDown))    
For Each cc In rc    
x = cc.Value    
With Worksheets("q").Columns("D:D")    
Set cfindq = .Cells.Find(what:=x, lookat:=xlWhole)    
If cfindq Is Nothing Then    
GoTo line1    
GoTo line2    
End If    
End With    
cc.Interior.ColorIndex = 6    
Next cc    
End With    
End Sub


Thanks to venkat1926 for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Published by aakai1056. - Latest update by deri58
This document entitled « Excel - Compare columns to find missing data » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.