Column Comparison Macro

[sleuth]

I want to write a macro to compare two columns on two separate sheets.
I want to search values in a column against a master list of valid entries for values from another column. The idea is that if a value on the first list does not appear on the second list, then that value is invalid. If it is, I want to copy the entire row for that invalid value and paste it onto another sheet containing a list of invalid entries along for a manual review.

I'm pretty sure I have to create 2 arrays containing these columns and I need to use 2 nested for loops. I've always struggled with loops and I'm new to VBA and need some help. This is what I have so far.

Sub Find_Invalid_Entries()
    Dim r1 ' last row of the list with survey results
    Dim r2 ' last row of the distribution list
    Dim Range1 As Range
    Dim Range2 As Range
    Dim shtC As Worksheet
    Dim shtB As Worksheet
    Dim C As Integer               'to store the column number of column C in "Survey Results (Raw)"
    Dim B As Integer               'to store the column number of column B in "Distribution List Check"


    Set shtC = Sheets("Distribution List Check") 'storing the sheets...
    Set shtB = Sheets("Survey Results (Raw)")


    shtB.Activate 'no matter you are in the workbook, always run from the sheet B


    r1 = Range("C2").End(xlDown).Row 'the last row of the list with the survey results
                                    
    Set disRange1 = Range(Cells(1, 2), Cells(r1, 2)) 'here need to change the 2 for 1 if you do not want headers
    C = 3 'column C and B, just the numbers
    B = 2


    shtC.Activate 'go to sheet B
    
    r2 = Range("B2").End(xlDown).Row
    Set disRange2 = Range(Cells(1, 2), Cells(r2, 2))
    
    ' Thinking I need to put nested for loops here


End Sub

 

[sleuth]

Ok, with the code below, the first msgbox shows me I now have a range of numbers in Range("B:B") for the active sheet (shtB). The If Statement still takes me to the "No Invalid Entries Found" msgbox.

Added a msgbox to show the range of shtC as well, and that in fact does display the range. So I believe both sht B and sht C ranges are properly defined before going into the For Each loop. I'm thinking the problem may be with the copy/paste statement.

 

[sleuth]

Taking a break for lunch. Here's where I am.
Still thinking there's a problem with the Copy/Paste statement but haven't figured it out yet.

Sub Find_Invalid_Entries()
    Dim shtC As Worksheet
    Dim shtB As Worksheet
    Dim c As Range
    Dim i As Integer
    Set shtC = Sheets("Survey Results (Raw)")
    Set shtB = Sheets("Distribution List Check")
    
    ' MsgBox Join(Application.WorksheetFunction.Transpose(shtB.Range("B:B").Value), Chr$(10)) ' For debugging, display contents of Range(B:B) on shtB.
    ' MsgBox Join(Application.WorksheetFunction.Transpose(shtC.Range("C2", shtC.Cells(Rows.Count, 3).End(xlUp)).Value), Chr$(10)) ' For debugging, display contents of Range C2 to Last Row on shtC.
    
        For Each c In shtC.Range("C2", shtC.Cells(Rows.Count, 3).End(xlUp))
                i = 0
                If shtB.Range("B:B").Find(c.Value, , xlValues, xlPart) Is Nothing Then
                    c.EntireRow.Copy Sheets("Invalid Responses").Cells(Rows.Count, 1).End(xlUp)(2)
                    MsgBox ("Invalid Supplier Code Entry: " & c)
                    i = 1
                End If
                
        Next
        
    If i = 1 Then
        MsgBox ("Invalid Entries copied to 'Invalid Responses' tab for review.")
    End If
End Sub

 

[sleuth]

Success!

Here's what worked. I have some work to do to make it robust but I got the basic function down.

Sub Find_Invalid_Entries()
    Dim shtC As Worksheet
    Dim shtB As Worksheet
    Dim c As Range
    Dim i As Integer
    Set shtC = Sheets("Survey Results (Raw)")
    Set shtB = Sheets("Distribution List Check")
    
    ' MsgBox Join(Application.WorksheetFunction.Transpose(shtB.Range("B:B").Value), Chr$(10)) ' For debugging, display contents of Range(B:B) on shtB.
    ' MsgBox Join(Application.WorksheetFunction.Transpose(shtC.Range("C2", shtC.Cells(Rows.Count, 3).End(xlUp)).Value), Chr$(10)) ' For debugging, display contents of Range C2 to Last Row on shtC.
    
        For Each c In shtC.Range("C2", shtC.Cells(Rows.Count, 3).End(xlUp))
                i = 0
                If shtB.Range("B:B").Find(c.Value, , xlValues, xlPart) Is Nothing Then
                    c.EntireRow.Copy Destination:=Sheets("Invalid Responses").Range("A" & Rows.Count).End(xlUp).Offset(1)
                    ' MsgBox ("Invalid Supplier Code Entry: " & c)
                    i = 1
                End If
                
        Next
        
    If i = 1 Then
        MsgBox ("Invalid Entries copied to 'Invalid Responses' tab for review.")
    End If
    
    Range("A1").Select
    
End Sub

 

[JLGWhiz]

I think your message box might be worded wrong. As I understood your OP, the idea was to look at sheet 2 Col B to see if a value on sheet 1 Col C was not there, and if not, then list the item from sheet 1 onto sheet 3 as invalid value. If I have misunderstood the objective, let me know. But if I am correct, then it should be listing invalid entries onto sheet 3 as written.

 

[sleuth]

I think your message box might be worded wrong. As I understood your OP, the idea was to look at sheet 2 Col B to see if a value on sheet 1 Col C was not there, and if not, then list the item from sheet 1 onto sheet 3 as invalid value. If I have misunderstood the objective, let me know. But if I am correct, then it should be listing invalid entries onto sheet 3 as written.

It wasn't sheet 1 and sheet 2, but basically the idea was to look at "Distribution List Check" Col B (shtB) for values of "Survey Results (Raw)" Col C (shtC). If those values were not there, then list items from shtC onto sheet "Invalid Responses".

You got me 90% of the answer, but for some reason the copy statement wasn't working. I also didn't have a good understanding of the IS NOTHING statement and how to use it, so I ended up doing some stuff in your for loop that didn't work. I think I basically wound up where you started me except a slightly different copy/paste statement.

Thanks for your help regardless. Couldn't have figured it out if you hadn't pointed me in the right direction.

 

[JLGWhiz]

It wasn't sheet 1 and sheet 2, but basically the idea was to look at "Distribution List Check" Col B (shtB) for values of "Survey Results (Raw)" Col C (shtC). If those values were not there, then list items from shtC onto sheet "Invalid Responses".

You got me 90% of the answer, but for some reason the copy statement wasn't working. I also didn't have a good understanding of the IS NOTHING statement and how to use it, so I ended up doing some stuff in your for loop that didn't work. I think I basically wound up where you started me except a slightly different copy/paste statement.

Thanks for your help regardless. Couldn't have figured it out if you hadn't pointed me in the right direction.

The code I suggested is using a Find statement without a specified property, so it would be a range object if found. If not found, it would have to be Nothing, because no other value can be applied to that particular statement. Therefore, If the seach object is not found, the statement would return Nothing and you would know that your item was not on the other list.