Find highest value in Column V for unique value in column R

Small example of the columns:



ABCD 1
ABCD 1
ABCD 2
ABCD 0
DCBA 0
DCBA 1
DCBA 1
PPPP 0
PPPP 1
PPPP 2
PPPP 2
LLLL 0
LLLL 0
LLLL 1

I need end up with the rows only with the highest number in the last column, so it should look like this after:

ABCD 2
DCBA 1
DCBA 1
PPPP 2
PPPP 2
LLLL 1


I am sure this is some easy small code but I don't know where to start on this one really.... thanks!

Yes, I only want to keep (or extract to another page, but deleting the rows seems easier) the rows for each unique value in the first column with the highest number (all of the highest numbers not just one, as you see in the example there are many of the same highest number I need all of those rows now just one of them).

It seems filtering wouldn't work for such a situation so I was looking at doing a quick macro but I'm not sure where to start.... doing it by hand would be ridiculous for me as there are around 20,000 rows on the sheet.

Thanks!

Originally Posted by Jakobshavn

Are you sure you want all the other rows deleted??

I am thinking it will have to be something like this, but I'm not sure of the functions availible for it... I don't really have any experience in VB, just Java / C++ and that is minimal.

My two ideas for doing it would be:

Create some sort of array (i know about these, dont really know how to work them though) that would create variable for each "unique value" from the first column.

Then have the macro search every row and determine the highest value from the second column, say "2" or "3" or whatever.

Then have the macro go through all the rows a second time and delete all rows for a given unique value that do not match the discovered highest value "2" or "3" or whatever.

I know it should be possible, and not too difficult for an experienced VB writer, but I have no idea how to make this sort of array or whatever and where to start on this....

Thanks!

Here is my attempt so far... but my brain hurts right now. There is one error saying "next without for statement" which makes no sense because I have that...

The other problem is its not actually getting the highest replay per each unique value, it keeps reseting it to 0.

The other HUGE problem, is this macro to run is going to loop 246924000000 times, which is just way to freaking slow, is there a way to make it so there are not these loops inside of loops or something?

Thanks

Sub Thing()


'Define Global Variables
BeginRow = 2
EndRow = 19000
Count = 1
UniqueCountMax = 648




'Loop For Each Unique Value
For UniqueValueLoop = Count To UniqueCountMax

    'Select Highest Replay
    With Worksheets("Unique Values")
    refid = .Range("A" & Count)
    End With

    For RowCnt = BeginRow To EndRow
        'Determine Highest Replay
        
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "1") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 1
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "2") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 2
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "3") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 3
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "4") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 4
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "5") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 5
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "6") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 6
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "7") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 7
        End If
        If (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 22).Value = "8") And (ActiveWorkbook.Worksheets("Data").Cells(RowCnt, 18).Value = refid) Then
            HighReplay = 8
        End If
    
    
        'Print Highest Number For Each Unique Value
        ActiveWorkbook.Worksheets("Unique Values").Cells(Count, 2) = HighReplay
        
        'Delete Row For Each Unique Value if not highest replay
        
            'For DeleteRow = BeginRow To EndRow
            '    If (ActiveWorkbook.Worksheets("Data").Cells(RowCntSecondary, 18).Value = refid) And (ActiveWorkbook.Worksheets("Data").Cells(RowCntSecondary, 22).Value <> HighReplay) Then
            '    Cells(RowCntSecondary, "A").EntireRow.Delete
          '  Next DeleteRow
          
    Next RowCnt
    
    Count = Count + 1
Next UniqueValueLoop




End Sub