0

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?

  • I don't understand why the "helper column" method is not feasible. But if you cannot insert an extra column, you could use the Advanced Filter instead and either filter in place or to a new location. And I would use COUNTIFS -- then you don't need to concatenate. – Ron Rosenfeld Oct 16 '15 at 23:59
  • As I read your question, I started thinking, “How would I answer this?”  And my response was to use a helper column to count unique values and filter on it — exactly what you have done.  I can’t give you a better answer unless you explain better why that approach doesn’t work. – Scott Oct 17 '15 at 7:13
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.