VBA - Count Two Column's Duplicate Values and Tagged it

Question

I'm doing a program where i need to count the duplicate values from Column A and Column B if the duplicate value from both columns are greater than 1 tag it in two columns, the first column as "selected" the second one is "updated" the one that will be tagged in the duplicate values is the date that closest to current date..

Example:

Column A| Column B| Column C | Column D| Column E|
  1     |  easy   | 1/2/2016 |         |         |
  2     |  normal | 1/3/2016 |         |         |
  2     |  hard   | 1/4/2016 |         |         |
  1     |  easy   | 1/5/2016 |         |         |

Output:

Column A| Column B| Column C | Column D | Column E|
      1 |  easy   | 1/2/2016 |          |         |
      2 |  normal | 1/3/2016 |          |         |
      2 |  hard   | 1/4/2016 |          |         |
      1 |  easy   | 1/5/2016 | selected | updated |

In the sample output above Column A and B have duplicate values which 1 and easy row 4 have been tagged as Selected and updated because its the closest date today.. if column A and B are not the same value like 1,normal and 1 ,hard no action done

My code(EDITED):

    Sub sample1()

    Dim i As Long, lastRow As Long, countRow As Long, countRow1 As Long
    Dim Var1 As Integer

    With Worksheets("Sheet1")

   lastRow = .Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To lastRow
            countRow = Application.CountIf(.Columns(1), .Cells(i, 1))
            countRow1 = Application.CountIf(.Columns(2), .Cells(i, 2))

            If countRow > 2 Then
                If Not CBool(Application.CountIfs(.Columns(1), .Cells(i, 1), _
                                        .Columns(3), ">" & .Cells(i, 3))) Then _
                    .Cells(i, 4) = "selected"

            If countRow1 > 2 Then
                If Not CBool(Application.CountIfs(.Columns(2), .Cells(i, 2), _
                                        .Columns(3), ">" & .Cells(i, 3))) Then _

                    .Cells(i, 5) = "updated"
           End If
            End If
            End If
        Next
    End With

End Sub

The countifs is now working by one column only what i need is a pair of duplicate values in a two column, like in the sample output: Column A and Bthe value 1 and easyare the same in row 2 and 4 that why its been tag my code tag them separetely. please help me about this!

Answer 1

There were some lines of code that I couldn't reconcile so I've removed them in order to provide a simplified solution.

Sub two_column_dated_duplicates()
    Dim i As Long, lastRow As Long

    With Worksheets("Sheet1")
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row

        For i = 2 To lastRow
            If Application.CountIfs(.Columns(1), .Cells(i, 1), _
                                          .Columns(2), .Cells(i, 2)) > 1 Then
                If Not CBool(Application.CountIfs(.Columns(1), .Cells(i, 1), _
                                                  .Columns(2), .Cells(i, 2), _
                                                  .Columns(3), ">" & .Cells(i, 3))) Then
                    .Cells(i, 4).Resize(1, 2) = Array("selected", "updated")
                End If
            End If
        Next
    End With
End Sub

          
            ^{Data before two_column_dated_duplicates()              Data after two_column_dated_duplicates()}

Answer 2

countRow seems to be the problem. You are reading it a lot in the If statements but you do not seem to be setting it anywhere. I am guessing that you are taking the current entry and counting how many entries of that value are in the column. If there are more than one entry then there are duplicates In which case I am guessing that your problem line is

columnA = Application.CountIf(.Column(1), .Cells(i, 1))

Should this not be

countRow= Application.CountIf(.Column(1), .Cells(i, 1))

Your indentation could be better to make it easier to read. Also you have:

If countRow > 1 Then
Else         If countRow > 1 Then
End If"

Your If and Else have the same conditions. I cannot see what the Else is doing since it will simply set columnA (countRow?) that has already been set.

You also seem to have 2 loops doing, essentially, the same thing.

You have a variable i that is read but never set and seems to be a duplicate rowcounter

I think you need to start again and understand what the code needs to do. In pseudocode you want this:

Loop through every row in the table
  if Count of data in current row, column 1 > 1 then
    if Count of data in current row, column 2 > 2 then
        set current row, column 4 = "Selected"
        set current row, column 5 = "Updated"
     endif
   endif
end loop

Your code should only be 10 or 12 lines and involve a single loop with a coupled of nested ifs.