Excel - Compare columns to find missing data

Ask a question

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.


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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team