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?

DataFilterAdvanced Filter.

Unique records only and copy to another location.


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"
wrote:

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?

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

"Gord Dibben" wrote:

DataFilterAdvanced Filter.

Unique records only and copy to another location.


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"
wrote:

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?

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

"Brian Glusovich" <Brian wrote in
message ...
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

"Gord Dibben" wrote:

DataFilterAdvanced Filter.

Unique records only and copy to another location.


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"

wrote:

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?

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

--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
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

"Brian Glusovich" <Brian wrote in
message ...
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

"Gord Dibben" wrote:

DataFilterAdvanced Filter.

Unique records only and copy to another location.


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"

wrote:

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?

I tried this but is does not hide the duplicates. Can you help?

"Gord Dibben" wrote:

DataFilterAdvanced Filter.

Unique records only and copy to another location.


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"
wrote:

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?