Comparing Column:A(Sheet1) with Column:B(Sheet2) and printing the unmatched values in Column:B(Sheet2)

[Jabes7]

Hi to all Brainee Folks,

I have a piece of VBA code which compares Column:A(Sheet1) with Column:B(Sheet2) and prints the unmatched values in Column:B(sheet2). I used resize to fetch the entire row of Sheet1( unmatched values) and writes in Sheet2.

Now my Immediate requirement is, i need to check for condition on Column:C(Sheet1) if the row value is 'Yes', it can fetch the unmatched values of appropriate rows of Sheet1 and can write in Sheet2. Kindly Help

Sub NotFoundList()

    Dim AWF As WorksheetFunction: Set AWF = WorksheetFunction
    Dim cell As Range
    Dim s1LastRow As Long
    Dim s2LastRow As Long
    Dim s3RowCount As Long
    Dim S2RowCount As Long
    Dim s3 As Long


    With Sheets("Sheet1")
        s1LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With


    With Sheets("Sheet2")
        s2LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        S2RowCount = s2LastRow + 1
    End With


    With Sheets("Sheet1")
        For Each cell In .Range("A1:A" & s1LastRow)
            If AWF.CountIf(Sheets("Sheet2").Range("B1:B" & s2LastRow), cell.Value) = 0 Then
                Sheets("Sheet2").Range("B" & S2RowCount).Resize(, 4) = cell.Resize(, 4).Value
                S2RowCount = S2RowCount + 1
            End If
        Next
    End With
End Sub

 

[patel45]

attach please a sample of your file, before an after macro effect

 

[AlphaFrog]

Now my Immediate requirement is, i need to check for condition on Column:C(Sheet1) if the row value is 'Yes', it can fetch the unmatched values of appropriate rows of Sheet1 and can write in Sheet2. Kindly Help

I'm not sure I follow your new criteria. This tests if the Sheet1.columnC value is "Yes", then tests if the value is unmatched as before.

    With Sheets("Sheet1")
        For Each cell In .Range("A1:A" & s1LastRow)
[COLOR=#ff0000]            If LCase(cell.Offset(, 2).Value) = "yes" Then[/COLOR]
                If AWF.CountIf(Sheets("Sheet2").Range("B1:B" & s2LastRow), cell.Value) = 0 Then
                    Sheets("Sheet2").Range("B" & S2RowCount).Resize(, 4) = cell.Resize(, 4).Value
                    S2RowCount = S2RowCount + 1
                End If
[COLOR=#ff0000]            End If[/COLOR]
        Next
    End With

 

[HalfAce]

I'm thinking along the same lines as AlphaFrog.

Sub NotFoundList()    Dim AWF As WorksheetFunction: Set AWF = WorksheetFunction
    Dim cell As Range
    Dim s1LastRow As Long
    Dim s2LastRow As Long
    Dim s3RowCount As Long
    Dim S2RowCount As Long
    Dim s3 As Long

    With Sheets("Sheet1")
        s1LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With

    With Sheets("Sheet2")
        s2LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        S2RowCount = s2LastRow + 1
    End With

    With Sheets("Sheet1")
        For Each cell In .Range("A1:A" & s1LastRow)
            If AWF.CountIf(Sheets("Sheet2").Range("B1:B" & s2LastRow), cell.Value) = 0 [COLOR=#ff0000]_
               And cell(, 3).Value = "Yes"[/COLOR] Then
                    Sheets("Sheet2").Range("B" & S2RowCount).Resize(, 4) = cell.Resize(, 4).Value
                    S2RowCount = S2RowCount + 1
            End If
        Next
    End With
End Sub

Hope it helps.