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

by Achyutha Mohan   Last Updated October 19, 2015 13:00 PM

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:

My method

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?



Answers 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.

jcbermu
jcbermu
October 19, 2015 10:32 AM

Related Questions


Listbox, mouse wheel scroll issue

Updated October 22, 2015 08:00 AM

Formula issue Microsoft Excel

Updated July 02, 2015 16:00 PM

If Difference Higher or Lower than Display

Updated August 03, 2015 15:00 PM


How to fix Excel Formula

Updated July 12, 2017 09:01 AM