asked on

To remove duplicate line if this line is based on a repeated route and also add 'Y' in column F to indicate that the next line was removed.

To remove duplicate line if this line is based on a repeated route and also add 'Y' in column F to indicate that the next line was removed.

One can filter column b, c and d to be all equal to 0 and will see all the same route appear in column A.

See attached for detailed examples and comments.

And of course if there are more than 1 duplicates meaning like this:

108.004.600 - 108.004.600      0      0      0      84.75291788
108.004.600 - 108.004.600      0      0      0      1.968908303
108.004.600 - 108.004.600      0      0      0      2.417233924
108.004.600 - 108.004.600      0      0      0      1.713124927

then the result should be like this (3 lines below are removed) :
108.004.600 - 108.004.600      0      0      0      84.75291788 Y

and column G states how many lines were removed.

if there is no duplicate, for example:
405.091.200 - 405.091.200      0      0      0      6.337535366

then do nothing for this line.

Same concept applies to all.

Thanks !
Row-Indicator-Remove-Duplicate-EE.xlsx

Subodh Tiwari (Neeraj)

Please give this a try...

Sub RemoveDuplicates()
Dim lr As Long, i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 3 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) And Cells(i, 2) = 0 And Cells(i, 3) = 0 And Cells(i, 4) = 0 Then
        Cells(i - 1, 6) = "Y"
        Rows(i).Delete
    End If
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

In the attached, click the button called "Remove Duplicates" to run the code.
Row-Indicator-Remove-Duplicate-EE.xlsm

bluue s

ASKER

Thanks, but how many column G ?

It should state how many lines were removed for each duplicate. (see initial attached example)

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bluue s

ASKER

super !
Thanks alot !