SOLVED

Excel fails to remove dupllicates in simple file

Copper Contributor

I'm using the Remove Duplicates function on simple data sets (usually 20-40 items). Excel fails to remove duplicates, and the feedback received in 'completed' dialogs doesn't seem to be consistent or correct.


In video clip (<1:30), I created a new document with simple data. Blocks of data are simply copy/pasted to create the duplicates. I select the columns to analyze, and results are shown, clearly removing some duplicates but not all, even after multiple runs.

I'll start with the question
"What am I not doing right?"

 

I've also tried

  • running on single columns (fails)
  • running on selections (fails)
  • running with and without column headers selected
  • Verifying that no Grouping is involved
    __
    Excel for Mac version 16.51 (21071101) <-- current update
6 Replies

@Brett745 

In the Remove Duplicates dialog, you leave all columns checked. As a consequence, Excel will look for rows that are duplicate in all columns A to D. The 12 rows that it reports as removed each time are the empty rows at the bottom.

If you want to remove entire rows if column A is duplicate, select columns A to D (or the entire sheet) and clear the check boxes for columns B, C and D in the dialog.

If you want to remove duplicates from each individual column, select each column in turn and remove duplicates.

Hans, thank you!
Is there a method that within a selection (such as the entire data set, or multiple selected cells, or multiple selected columns) I could evaluate and remove all the duplicates, regardless of column or row location? (ie: If Cat is repeated anywhere, one instance is kept and the duplicates are removed)

@Brett745 

Do you want to clear the cells with duplicates, or delete them and move the cells below upwards, or delete them and move the cells to the right leftwards, or ...?

So many options!
With my small data sets I could be happy with any of those, but let's aim at "delete them and move the cells upwards"
For my needs, each column is a list of related items being 'picked' for orders, and we only need to pull a whole box of each unique item to later fill specific orders with. Stated differently, there is no relationship between the cell contents of any particular row, and it's not a database record such as name/address/item that we wouldn't want to scramble.
best response confirmed by Brett745 (Copper Contributor)
Solution

@Brett745 

Run this macro. Please test on a copy of your data first.

Sub RemoveDups()
    Dim rng As Range
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Range("A:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rng = Range("A2:D" & m)
    For c = 4 To 1 Step -1
        m = Cells(Rows.Count, c).End(xlUp).Row
        For r = m To 2 Step -1
            If Application.CountIf(rng, Cells(r, c).Value) > 1 Then
                Cells(r, c).Delete Shift:=xlShiftUp
            End If
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub
Macro works well. I"ll have to learn how to expand it to more columns, but I think the Set rng and c will get me there.
THANK. YOU for your willingness to help me out -- it is appreciated.
1 best response

Accepted Solutions
best response confirmed by Brett745 (Copper Contributor)
Solution

@Brett745 

Run this macro. Please test on a copy of your data first.

Sub RemoveDups()
    Dim rng As Range
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Application.ScreenUpdating = False
    m = Range("A:D").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rng = Range("A2:D" & m)
    For c = 4 To 1 Step -1
        m = Cells(Rows.Count, c).End(xlUp).Row
        For r = m To 2 Step -1
            If Application.CountIf(rng, Cells(r, c).Value) > 1 Then
                Cells(r, c).Delete Shift:=xlShiftUp
            End If
        Next r
    Next c
    Application.ScreenUpdating = True
End Sub

View solution in original post