Excel fails to remove dupllicates in simple file

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

Excel not removing duplicates in simple example.mp424.9 MB

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

HansVogelaar
MVP
Jul 15, 2021
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.
- Brett745
  Copper Contributor
  Jul 15, 2021
  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)
  - HansVogelaar
    MVP
    Jul 15, 2021
    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 ...?

Forum Discussion

Excel fails to remove dupllicates in simple file

Share

Resources