I am looking to compare Column A in Sheet1 with Column A in Sheet2. I want to find unique values in Sheet1 Column A that are not present in Sheet2 Column A, and I want to extract the entire rows of the unique values (or just Columns A-D if that's easier) into Sheet 3.
Basically, if you are familiar with "Find Unmatched Query Wizard" in Access, I am trying to replicate that in Excel with a VBA in order to speed up my process. For example, I have a list of names (e.g. Smith, John) in Sheet1 Column A and a list of names in Sheet2 Column A. I want to find unique names that are in Sheet1 Column A but NOT in Sheet2 Column A. Then, I want the entire row (with data from columns A,B,C and D) of the unique Sheet1 Column A names to be organized on a new sheet.
Sub CrossCheck()
Dim Sheet1, Sheet2, r As Range
Dim x, Cn, Dn As Long
Set Sheet1 = Range("A65536").End(xlUp)
Set Sheet2 = Range("B65536").End(xlUp)
Cn = 1
Dn = 1
For x = 1 To Sheet2.Row
Set r = Columns("B").Find(Cells(x, 1), , xlValues, xlWhole)
If r Is Nothing Then
Cells(Cn, 3) = Cells(x, 1)
Cn = Cn + 1
End If
Next
For x = 1 To Sheet1.Row
Set r = Columns("A").Find(Cells(x, 2), , xlValues, xlWhole)
If r Is Nothing Then
Cells(Dn, 4) = Cells(x, 2)
Dn = Dn + 1
End If
Next
End Sub
The above code comes close to solving this, but it can only be used between columns on one sheet instead of on two sheets; also, it only does the unique value and not the entire row. Thus, I still have three problems. The first problem is that instead of comparing two different columns on the same sheet, I would like to compare two different columns on two separate sheets. The second problem is that I need not just the data from the unique value in column A, but I need it for the entire row (Columns A-D). The third problem is that I need these unique rows sent to Columns A-D on a new sheet.
Bookmarks