Excel Duplicate File name with different Revisions I have an Excel where i have file names in one column with the revision numbers in another column. I need to remove the duplicates(file with higher version should be retained and lowerversion deleted).Can you guys help me with excel VBA for this.

Eg Raw Data



Desired output from macro



What i have tried( i am new to VBA , tried the following with help of google)

with Sheets("Sheet1")
    LRow1 = .Cells(Rows.Count, 1).End(xlUp).row
    LRow3 = "A1:" &amp; getColumnAlpha &amp; LRow1

     For d = 2 To LRow1
    Cells(d, 4) = Cells(d, 1) &amp; "_" &amp; Cells(d, 3)
    Next d
    Range("A2:F" &amp; LRow1).Sort Key1:=Range("D2:D" &amp; LRow1), order1:=xlAscending, Header:=xlNo
    'Sheets("Sheet1").Columns(4).Copy Destination:=Sheets("Test").Columns(2)
    Dim test As String
    test = LRow1
    finishedRow = LRow1
    'finishedRow() = test.ToStringArray
    'ReDim finishedRow(LRow1) As String
    row = 1
    resultRow = 1
    index = 1

    Do While .Range("D" &amp; row) &lt;&gt; ""

      If UBound(Filter(finishedRow, row)) &lt; 0 Then
    finishedRow = row
    index = index + 1
    innerRow = 1

    Do While .Range("D" &amp; innerRow) &lt;&gt; ""


        If UBound(Filter(finishedRow, innerRow)) &lt; 0 Then

            If .Range("D" &amp; row) = .Range("D" &amp; innerRow) Then

                If .Range("B" &amp; row) &lt; .Range("B" &amp; innerRow) Then
                'Update time in result record
                'Sheets("Sheet1").Range("B" &amp; resultRow) = "Delete"
                 Rows(resultRow).EntireRow.Delete


                End If

                            'Add row to record array
                            finishedRow = innerRow
                            index = index + 1

                        End If

                    End If

                    'Increase inner row
                    innerRow = innerRow + 1

                Loop

                'Increase result row
                resultRow = resultRow + 1

            End If

            'Increase row
            row = row + 1
    Loop




End With


This code removes duplicates but requires the macro to be run multiple times
Although this question is slightly off-topic I will give you a run down of your code. I will start with some bugs in your code, then make some general remarks about the coding style, explain what is probably causing your code to miss duplicates and, finally, propose an approach that removes all duplicates in one go. 

Bugs

The bugs I referred to above are that you frequently refer to cells on the ActiveSheet instead to those on Sheet1 because you have omitted the dot in front. 

Style

Now, let me say something about the coding style. 


You should indent your code properly. In your code the start and end of if blocks and loops do not match up, which makes it very hard to read. 
One of the best practices to improve readability of your code is to use meaningful names. E.g. LRow3 does not tell you at all what it is. (It containing 'row' I would expect it to be a Long, but actually it is a String. (I hope you declared it above the code you posted.)) As far as I can see it might better be called sometime like dataRangeString. If anybody else ever reads this again, or you in a few months, he will be grateful for the added readability. 
This is not a major concern here, but try to follow the single responsibility principle, which basically tells you that a unit of code should do one and only one thing. E.g. the first for loop could be its own sub called SaveConcatenatedKeyInKeyColumn. 
In the With block variable you make an implicit reference to the ActiveWorkbook. It is good practice to make that explicit. Generally, you should avoid implicit references and default members, because they hinder readability and hide subtle bugs. 


For more help with the coding style you might want to have a look at Rubberduck. (Full disclosure, I am a contributer.) 

Potential Cause of Problems

Now to what I think might be the problem with your code. When you are deleting rows, all rows below get shifted up. This makes you skip consecutive rows that should have been deleted. 
To solve this, always remove rows bottom to top. 

Alternative Approaches

The approach I want to propose is to sort first by filename (Key1) and then by version (Key2). All you have to do then is to go from bottom to top and remove every row where the row below it has the same filename in the filename column. Because you sorted by version as the second key, only the highest version survives. 

Alternatively, you could use a Dictionary, which requires a reference to VBScript, to store the highest version seen so far in a first pass over your data. (Use Exists to see whether you have seen the filename before. If not, save the revision with the filename as key. If yes, compare the current revision for the filename to that in the dictionary and save it if it is higher.) In a second pass, from bottom to top, remove every row where the version is not the one in the dictionary. 

The second approach is faster for large data sets since you do not have to sort. However, if you want to sort the filenames anyway, as your example suggests, there is no real performance advantage.