Clear duplicate content in xl column

Copper Contributor

I am trying to clear duplicate content in the cells in column B of my xl table.

 

Remove Duplicates function is not suitable because it deletes rows.

 

I only need to clear the duplicate content in the cells from column B.

5 Replies

@AStamenkov 

Select column B only and then click Remove Duplicates on the Data tab of the ribbon.

Excel may display a warning:

S1705.png

Select 'Continue with the current selection', then click Remove Duplicates...

Excel will leave the other columns alone.

@AStamenkov 

My previous reply won't work if your data are in a table, since the data in a table row belong together. Deleting duplicates in a single column of a table makes no sense.

If you really need to do that, your data shouldn't have been in a table in the first place, so convert the table to a range. Then follow the steps from my previous reply.

What do you mean by clear? Are you wishing to hide duplicate items? If so then use conditional formatting.

@AStamenkov 

If you want to clear duplicate cells in column B:

Sub ClearDups()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B" & Rows.Count).End(xlUp).Row
    For r = 3 To m
        If Application.CountIf(Range("B2:B" & r - 1), Range("B" & r).Value) Then
            Range("B" & r).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

@AStamenkov 

An alternative could be Power Query.

clear values.JPG