Snasui Excel Forum

Code: Select all

Application.CountIfs(rAll, r, rAll, r.Offset(0, 1)) = 0 And Application.CountIf(rTarget, r) = 0

Code: Select all

Sub ListDup()

    Dim rAll As Range
    Dim r As Range
    Dim rTarget As Range
    
    
    With Sheets("Sheet1")
        Set rAll = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
        
    With Sheets("Sheet2")
        Set rTarget = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    End With
    
    With Sheets("Sheet1")
        For Each r In rAll
        If Application.CountIfs(rAll, r, rAll, r.Offset(0, 1)) = 0 And Application.CountIf(rTarget, r) = 0 Then
            
            With Sheets("Sheet2").Range("c" & .Rows.Count).End(xlUp)
               
                Range("c" & Rows.Count).End(xlUp).Offset(1, 0) = r
                Set rTarget = Range("c2", Range("c" & Rows.Count).End(xlUp))
                Range("d" & Rows.Count).End(xlUp).Offset(1, 0) = r.Offset(0, 1)
                
            End With

                End If
        Next r

    End With

End Sub

Code: Select all

'Other code
For Each r In rAll
    If Application.CountIfs(Sheets("Sheet2").Range("c:c"), r.Value, _
        Sheets("Sheet2").Range("d:d"), r.Offset(0, 1).Value) = 0 Then
        With Sheets("Sheet2").Range("c" & .Rows.Count).End(xlUp)
            .Offset(1, 0).Resize(1, 2).Value = r.Resize(1, 2).Value
        End With
    End If
Next r
'Other code

Code: Select all

If Application.CountIfs(Sheets("Sheet2").Range("c:c"), r.Value, _
                            Sheets("Sheet2").Range("d:d"), r.Offset(0, 2).Value) = 0 Then

Code: Select all

Sub ListDup()

    Dim rAll As Range
    Dim r As Range
    Dim rTarget As Range
    
    
    With Sheets("Sheet1")
        Set rAll = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
        
    With Sheets("Sheet2")
        Set rTarget = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    End With
    
    With Sheets("Sheet1")
                    For Each r In rAll
                        If Application.CountIfs(Sheets("Sheet2").Range("c:c"), r.Value, _
                            Sheets("Sheet2").Range("d:d"), r.Offset(0, 2).Value) = 0 Then
                            With Sheets("Sheet2").Range("c" & .Rows.Count).End(xlUp)
                                .Offset(1, 0).Resize(1, 3).Value = r.Resize(1, 3).Value
                            End With
                        End If
                    Next r

    End With

End Sub

Code: Select all

'Other code
For Each r In rAll
    If Application.CountIfs(Sheets("Sheet2").Range("c:c"), r.Value, _
        Sheets("Sheet2").Range("d:d"), r.Offset(0, 2).Value) = 0 Then
        With Sheets("Sheet2").Range("c" & .Rows.Count).End(xlUp)
            .Offset(1, 0).Value = r.Value
            .Offset(1, 1).Value = r.Offset(0, 2).Value
        End With
    End If
Next r
'Other code

Code: Select all

Sub SH()

    Dim rAll As Range
    Dim r As Range
    Dim r1 As Range

    With Sheets("Sheet2")
        Set rAll = .Range("d2", .Range("d" & Rows.Count).End(xlUp))
    End With
    
    With Sheets("Sheet1")
        Set r1 = .Range("d2", .Range("d" & Rows.Count).End(xlUp))
    End With
    
    With Sheets("Sheet2")
    
        For Each r In rAll
    
            If Application.CountIfs(Sheets("Sheet1").Range("c:c"), r.Value, _
            Sheets("Sheet1").Range("a:a"), r.Offset(0, -2).Value, _
            Sheets("Sheet1").Range("b:b"), r.Offset(0, -1).Value) = 0 Then
        
        With Sheets("Sheet1")
               
            r1.Offset(1, 0).Value = r.Offset(0, 0).Value
                     
        End With
                End If
            Next r
        End With

Code: Select all

Dim rAll As Range, r As Range, rs1 As String, rs2 As String
Dim r1All As Range, r1 As Range, rt1 As String, rt2 As String

With Sheets("Sheet2")
    Set rAll = .Range("b2", .Range("b" & Rows.Count).End(xlUp))
End With

With Sheets("Sheet1")
    Set r1All = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With
For Each r1 In r1All
    rt1 = r1.Value & r1.Offset(0, 1).Value & r1.Offset(0, 2).Value
    rt2 = r1.Value & r1.Offset(0, 1).Value
    For Each r In rAll
        rs1 = r.Value & r.Offset(0, 1).Value & r.Offset(0, 2).Value
        rs2 = r.Value & r.Offset(0, 1).Value
        If rt1 = rs1 Then
            Exit For
        ElseIf rs2 = rt2 Then
            r1.Offset(0, 3).Value = r.Offset(0, 2).Value
            Exit For
        End If
    Next r
Next r1