Remove all entries with a duplicate

rowdy2k5

New Member
Joined
Nov 14, 2011
Messages
31
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.
Blad3
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.
 
Upvote 0
Hi rowdy2k5,

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

Code:
Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?642868-Remove-all-entries-with-a-duplicate
    
    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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top