How to identify last duplicate row in a set 1

[chtullu]

Hello everyone,
I need to identify the last row of each duplicate set that exists in a spreadsheet. The other duplicates of the set will be sent to a worksheet called Duplicate while the last duplicate of the set is retained. However, the use wants to be able to identify which rows in the original spreadsheet are members of duplicate sets. Here is the code

   For intRowCount = 1 To intTotalRows
        If (Cells(intRowCount, 10).Value = "Duplicate") Then
            
            If Not Cells(intRowCount + 1, 11) = Cells (intRowCount, 11) Then
                Cells(intRowCount, 10).Value = "OkDup"
                 x = x + 1
                 
            End If
        End If
              
    Next intRowCount
    MsgBox "X Counter is " & x
[code]

I using the variable X in order to track the number of last duplicates I've found.  It is only for test purposes.  When I run the code, the resultant value of X is zero.

 

[PHV]

Have you tried to execute your code step by step (debug mode) , eg just to check that the cell value is "Duplicate" ?
Anyway I'd replace this:
If Not Cells(intRowCount + 1, 11) = Cells (intRowCount, 11) Then
By this:
If Cells(intRowCount + 1, 11) <> Cells (intRowCount, 11) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chtullu]

Hi,
I've already tried that however, it looks like I found the solution

For intRowCount = 1 To intTotalRows
        If (Cells(intRowCount, 10).Value = "Duplicate") Then
            If Cells(intRowCount + 1, 10).Value = "Ok" Then
                x = x + 1
                Cells(intRowCount + 1, 10).Value = "OkDup"
            End If
        End If

I had earlier in the code identified all duplicates and place the value of "duplicate" in column 10, while setting the last duplicate to "Ok". So I decided to look for rows that had the string "Duplicate" in column 10, and then looked at the next row to see if the string in column 10 was "Ok". If so, then I changed it to "OkDup" Since I knew that the record following the last record in a duplicate set marked "Duplicate" precedes the last duplicate record"
It ain't pretty but it works. Thanks again for your help anyway.