|
|
|
|
|
|
|
|
|
|
| |
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:
> Data>Filter>Advanced Filter.
>
> Unique records only and copy to another location.
>
>
> Gord Dibben Excel MVP
>
> On Thu, 23 Jun 2005 14:47:04 -0700, "Amber" <Amber@discussions.microsoft.com>
> 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?
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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 Glusovich@discussions.microsoft.com> wrote in
message news:62C63CC9-D692-4433-B486-471F90B3ADDA@microsoft.com...
> 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:
>
>> Data>Filter>Advanced Filter.
>>
>> Unique records only and copy to another location.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"
>> <Amber@discussions.microsoft.com>
>> 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?
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Forgot to mention that B5 in my formula is the first data after the header
in column B
--
Regards,
Peo Sjoblom
"Peo Sjoblom" <peo.sjoblom@mvps.org> wrote in message
news:%23HTGzKgEJHA.1456@TK2MSFTNGP03.phx.gbl...
> 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 Glusovich@discussions.microsoft.com> wrote in
> message news:62C63CC9-D692-4433-B486-471F90B3ADDA@microsoft.com...
>> 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:
>>
>>> Data>Filter>Advanced Filter.
>>>
>>> Unique records only and copy to another location.
>>>
>>>
>>> Gord Dibben Excel MVP
>>>
>>> On Thu, 23 Jun 2005 14:47:04 -0700, "Amber"
>>> <Amber@discussions.microsoft.com>
>>> 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?
>>>
>>>
>
>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
Question
Answer
Notify me of replies