Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home

Removing duplicate entries from a column

Posted by Dick Riker on February 07, 2002 7:01 AM
I frequently extract a long column of numbers from a report one of our office programs produces into an Excel spreadsheet. The column frequently and correctly includes quite a few duplicate (even triplicate) three to six digit numbers. For my Excel purposes, though, I need to eliminate all but one of these duplicates and/or triplicates along with all of the other data in that row. Any suggestions beyond tedious eye-balling?

Re: Removing duplicate entries from a column

Posted by Larry on February 07, 2002 7:05 AM
***** Go to Data/Filter/Advanced Filter then choose Unique Records


Re: Removing duplicate entries from a column

Posted by Chris D on February 07, 2002 1:49 PM
if it's okay to eliminate the rest of the data in the row for any duplicates, you can also use conditional formatting which will highlight any duplicates in a single column :

say your data begins in a1 :

select cell A1
format (from menubar)
conditional formatting
change "cell value is" to "formula is"
type : =countif(a$1:a1000,a1)>1
format
patterns
choose the Ferrari red colour
okay
okay
still in cell A1, select the format painter (the yellow paintbrush)
paint this formatting down to the end of your range

any duplicates should now be bright red background and easy to delete

in this example it will check repetitions to cell A1000, just increase this to how ever many your range regularly covers (maybe its just me, but I take a stab at a maximum then double it cos you never know what's going to happen in the future)

obviously, paste the formatting as far down as needed also....

hope this also helps
Chris



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.