Home>
Sub duplicate ()
Dim rng As Range, r As Range, chkVal As Integer
Set rng = Range ("A1: A30")
Set ist = Range ("B1: B30")
For Each r In rng
For Each s In ist
    chkVal = Application.CountIfs (rng, r, ist, s)
        If chkVal>1 Then
            s.Offset (0, 1) .Value = "Duplicate"
        End If
    Next s
Next r
End Sub

I want to check if there are duplicates in multiple conditions in columns A and B in the list.
If even one duplicate is found, all the lines related to it will be checked.
Example: Row A, Row B
1st line ● △ Duplicate
Second line ● △ Duplication
3rd line ●
I would like to do, but the current line will contain "duplicate" up to the third line.
What is the cause?

  • Answer # 1

    How about looping first?
    The premise that we want to process each chunk of lines is broken.

    What about the following?

    Dim rng As Range, r As Range, chkVal As Integer
    set rng = Range ("A1: B30")
    Dim arng as Range: set arng = Range ("A1: A30")
    Dim brng as Range: set brng = Range ("B1: B30")
    dim row
    for each r in rng.rows
        chkVal = Application.CountIfs (arng, r.Range ("A1"), brng, r.Range ("B1"))
        If chkVal>1 Then
           r.Range ("B1"). Offset (0, 1) .Value = "Duplicate"
        End If
    next

  • Answer # 2

    How about separating functions and changing them to AND conditions?

    Before change:
    chkVal = Application.CountIfs (rng, r, ist, s)
    If chkVal>1 Then

    After change:
    chkVal1 = Application.CountIf (rng, r)
    chkVal2 = Application.CountIf (ist, s)
    If chkVal1>1 And chkVal2>1 Then

  • Answer # 3

    Simply using Cells,

    Dim i As Long 'for statement
    For i = 0 To 100
        If Cells (i, 2) .Value = Cells (i-1, 2) .Value Then '
             Cells (i, 3) .Value = "Duplicate"
        End If
    Next

    What should I do?

    In this case

    The combination of row A and row B is also important, so you need to twist a bit.

  • Answer # 4

    Why do you play For Each twice?