How do I delete repeated values (#Not remove duplicate values#) in a column excel?

Question

I have the following two columns of data:

Location    Value
    A         1
    A         1
    B         2
    C         2
    D         3
    D         3
    E         4
    E         4
    F         5
    G         5

If there is a duplicate combination of Location and Value, it has to be DELETED. If I use the remove duplicate option, the duplicates are removed but retains one of the repeated values and that is not favorable for me. My intended output is this:

Location    Value
    B         2
    C         2
    F         5
    G         5

Here is what I've thought and tried so far:

I concatenated the two columns and then used COUNTIF to get a count of the number of times a value is repeated. I can then filter the count column by only considering the counts that equal to 1. This is shown here:

However, this method of mine is not feasible dues to various other filters and sorting that has been done in my spreadsheet.

Are there any other suggestions to achieve the same results as I have?

Answer 1

Using VBA:

Public Function allDuplicates()
    Dim a As Application
    Set a = Application
    Dim arrayRows() As Boolean
    maxCol = 2
    firstRow = 2
    Dim wks As Worksheet
    Set wks = ActiveSheet
    wks.Application.ScreenUpdating = False
    totalRows = wks.Cells(Rows.Count, "A").End(xlUp).Row
    ReDim arrayRows(totalRows)
    For i = firstRow To totalRows
        theRow = Join(a.Transpose(a.Transpose(wks.Range(Cells(i, 1), Cells(i, maxCol)))), Chr(0))
        For j = i + 1 To totalRows
            theOtherRow = Join(a.Transpose(a.Transpose(wks.Range(Cells(j, 1), Cells(j, maxCol)))), Chr(0))
            If theRow = theOtherRow Then
                arrayRows(i) = True
                arrayRows(j) = True
            End If
        Next j
    Next i
    For i = firstRow To totalRows
        If arrayRows(i) = True Then
            wks.Rows(i).Clear
        End If
    Next i
    Range("A1:B" & totalRows).Sort key1:=Range("A2:A" & totalRows), order1:=xlAscending, Header:=xlYes
    wks.Application.ScreenUpdating = True
    Message = MsgBox("Finished", vbInformation)
End Function

Open Visual Basic /Macros, add a module under ThisWorkbook, paste the code on the right side and execute it.