Deleting all duplicate items in a list

tmitchel

New Member
Joined
Jul 24, 2007
Messages
8
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
Use Advanced Filter, with the Unique Values and copy to another location options
 

tmitchel

New Member
Joined
Jul 24, 2007
Messages
8
That still leaves my non-taxable items in the list. All that does is delete one of the two duplicates
 

tmitchel

New Member
Joined
Jul 24, 2007
Messages
8
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

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2007
Messages
8
I have tried that formula and for some reason it doesn't highlight the ones I need it to.
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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

New Member
Joined
Jul 24, 2007
Messages
8
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!!
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

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
Top