# Remove all entries with a duplicate

#### rowdy2k5

##### New Member
Hello!

Is there something inbuilt in Excel 2010 which removes all entries which have duplicates and its duplicates (so that only cells with unique entries remain)?

For instance, if you have a column with following data:

"
A
A
A
B
C
D
D
A
C
E
F
G
"

The result should be:

"
B
E
F
G
"

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Excel 2007 => data => remove duplicates.

No, that produces:

"
A
B
C
D
E
F
G
"

A
B
1
name
count
2
"
2
3
A
4
4
A
4
5
A
4
6
B
1
7
C
2
8
D
2
9
D
2
10
A
4
11
C
2
12
E
1
13
F
1
14
G
1
15
"
2

<TBODY>
</TBODY>

Worksheet Formulas
Cell
Formula
B2
=COUNTIF(\$A\$2:A15,A2)
B3
=COUNTIF(\$A\$2:A15,A3)
B4
=COUNTIF(\$A\$2:A15,A4)
B5
=COUNTIF(\$A\$2:A15,A5)
B6
=COUNTIF(\$A\$2:A15,A6)
B7
=COUNTIF(\$A\$2:A15,A7)
B8
=COUNTIF(\$A\$2:A15,A8)
B9
=COUNTIF(\$A\$2:A15,A9)
B10
=COUNTIF(\$A\$2:A15,A10)
B11
=COUNTIF(\$A\$2:A15,A11)
B12
=COUNTIF(\$A\$2:A15,A12)
B13
=COUNTIF(\$A\$2:A15,A13)
B14
=COUNTIF(\$A\$2:A15,A14)
B15
=COUNTIF(\$A\$2:A15,A15)

<TBODY>
</TBODY>

<TBODY>
</TBODY>

And then filter on everything (except 1) and delete those rows.

You get the desired result.

Hi rowdy2k5,

I don't have Excel 2010, but this macro will do the trick:

Code:
``````Option Explicit
Sub Macro1()

Dim lngMyCol As Long, _
lngMyRow As Long

lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Application.ScreenUpdating = False

With Columns(lngMyCol)
With Range(Cells(2, lngMyCol), Cells(lngMyRow, lngMyCol))
.Formula = "=IF(AND(LEN(A2)>0,COUNTIF(\$A\$2:\$A\$" & lngMyRow & ",A2)>1),""DEL"","""")"
.Value = .Value
End With
.Replace "DEL", "#N/A", xlWhole
On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0 'Turn error reporting back on
.Delete
End With

Application.ScreenUpdating = True

End Sub``````

Just initially run the code on a copy of your data as the results cannot be undone if the results are not as expected.

Regards,

Robert

Replies
8
Views
667
Replies
0
Views
301
Replies
1
Views
383
Replies
16
Views
642
Legacy 143009
L
Replies
0
Views
225

1,212,099
Messages
6,105,955
Members
447,986
Latest member
dicklim39

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back