how can i get rid of duplicate records in excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large spreadsheet and because of system parameters, it has lots of
duplicate records. How can I get just one of each record without manually
deleting the duplicates?
 
Data>Filter>Advanced Filter.

Unique records only and copy to another location.


Gord Dibben Excel MVP
 
I've been having trouble with "advanced filter" for unique records (in Excel
XP) -- If I'm only filtering for one column, it's OK, but in trying to
filter, say 10 columns, with one or two columns for the criteria range, I get
incorrect results. E.g., selecting columns A--H, and using B and C as the
unique criteria, gives incorrect results. I've tried copying B and C to
another sheet, and filtering those only, and it works OK. Also using the
=If(COUNTIF($A$1:A1,A1)>1,"Dup","") approach indicated in another MS list,
and it works fine. Also, the new approach in Excel 2007 with data
tools/remove duplicates works fine (with the A--H, and criteria B,C example).
I've tried the criteria range in XP on several different worksheets, and get
the same wrong results. Am I misinterpreting what the criteria range is all
about, or ??
thanks,
Brian
 
Sounds like you are misinterpreting. Excel looks at all ranges and if one
row is

a a 1 2 3

and the next is

a a 4 5 6

than those are not unique values.


the criteria range cannot be 2 columns in the table

You create the criteria range and you can use a formula


assume you want to make sure B is filtering based on occurrence then you
create a criteria range out of the table, header for instance in J1 and the
formula in J2

Leave J1 blank (since we are using a formula) then use

=COUNTIF($B$5:B5,B5)=1

now apply the filter and as criteria range use $J$1:$J$2






--


Regards,


Peo Sjoblom
 
Forgot to mention that B5 in my formula is the first data after the header
in column B

--


Regards,


Peo Sjoblom
 
Back
Top