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?
Posted by Larry on February 07, 2002 7:05 AM
***** Go to Data/Filter/Advanced Filter then choose Unique Records
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
