Archive of Mr Excel Message Board
Back to Data in Excel archive index
Back to archive home
Removing duplicate entries from a columnPosted 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 columnPosted 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 columnPosted 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)
change "cell value is" to "formula is"
type : =countif(a$1:a1000,a1)>1
choose the Ferrari red colour
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
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.