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

Viewed 694

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!

2 Answers
Related