Deleting all duplicate items in a list

[tmitchel]

I am a tax auditor, and I am working with excel and I have two lists. One is a complete list of items, both taxable and non-taxable, the other is a list of just non-taxable. I highlighted the non-taxable sales and copied and pasted them under the main list, and I want to delete all the non-taxable items. Basically, the only duplicates in my list are non-taxable, and I need to delete ALL of them. What is the best way for me to do this?

 

[SteveO59L]

Use Advanced Filter, with the Unique Values and copy to another location options

 

[ADAMC]

Try here for duplicate info:

http://www.cpearson.com/excel/Duplicates.aspx

 

[tmitchel]

That still leaves my non-taxable items in the list. All that does is delete one of the two duplicates

 

[tmitchel]

There is nothing on the cpearson site that does what I need it to do.

An example of what I want is like this:

A
A
B
C
D
D
E
F
F
G

To end up like this:

B
C
E
G

 

[ADAMC]

Thats why i sent you a link to Chips page:

Based on your example above using a formula from chips page:

if i use this formula =COUNTIF($B$2:$B$11,B2)>1 in conditional formatting and set the format to red (Or any colour)
I am left with BCEG as per your example?

 

[tmitchel]

I have tried that formula and for some reason it doesn't highlight the ones I need it to.

 

[ADAMC]

Can you expand further in that case?
Based on your example it worked for me?

Which ones doesnt it highlight? Can you post some of your actual data i can test with?

 

[HalfAce]

Hello tmitchel, welcome to the board.
If you've no aversion to using a bit of vba for this (and assuming your list was in column A)
you might try something like this.

Sub DeleteDupes()
Dim LstRw As Long, iRng As Range, i As Range, DltRng As Range
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
Set iRng = Range(Cells(1, "A"), Cells(LstRw, "A"))
For Each i In iRng
  If WorksheetFunction.CountIf(iRng, i) > 1 Then
    If DltRng Is Nothing Then
      Set DltRng = i
    Else
      Set DltRng = Union(DltRng, i)
    End If
  End If
Next i
If Not DltRng Is Nothing Then DltRng.Delete Shift:=xlUp
End Sub

Hope it helps.

 

[tmitchel]

I figured it out, I used Chips method for sorting by cell colors, because I had one of the highlighted items bolded and red. Then I sorted so that the red items would have a duplicate above them. Then I used an If function to keep or delete the original item, deleted the ones I didn't want, then sorted again by color, and deleted the red ones. Thanks for your help!!