Remove all entries with a duplicate

[rowdy2k5]

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
"

 

[Oeldere]

Excel 2007 => data => remove duplicates.

 

[rowdy2k5]

No, that produces:

"
A
B
C
D
E
F
G
"

 

[Oeldere]

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.

 

[Trebor76]

Hi rowdy2k5,

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

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