Filter data per function

Hi,

I have the following problem: I want to filter my excel sheet fo
unique data. I have many columns with text-numbers (example ABC145-59
and I want to filter in such a way that only the unique values ar
remaining. I cannot use the data-filter function of Excel.

How can I do this?

Thanks Michie

--
vrijberge
-----------------------------------------------------------------------
vrijbergen's Profile: http://www.excelforum.com/member.ph...nfo&userid=2775
View this thread: http://www.excelforum.com/showthread.php?threadid=47266

You could use a helper column of formulas. But that formula depends on what you
mean by unique.

Do you want just the values that occur only once--or do you want each value when
it occurs the first time.

If your data is in A2:A999
You could insert a new column B and use a formula like:

=countif($a$2:$a$999,a2)
and drag down

And filter to show just the 1's (only the values that appear once will appear).

or...

=countif($a$2:a2,a2)
and drag down.

Then filter to show just the 1's. The visible rows will contain just the first
time that value appeared (no matter if it showed up once or hundreds of times).

You may want to read some of Chip Pearson's techniques for dealing with
duplicates:
http://www.cpearson.com/excel/duplicat.htm

vrijbergen wrote:
>
> Hi,
>
> I have the following problem: I want to filter my excel sheet for
> unique data. I have many columns with text-numbers (example ABC145-59)
> and I want to filter in such a way that only the unique values are
> remaining. I cannot use the data-filter function of Excel.
>
> How can I do this?
>
> Thanks Michiel
>
> --
> vrijbergen
> ------------------------------------------------------------------------
> vrijbergen's Profile: http://www.excelforum.com/member.ph...fo&userid=27757
> View this thread: http://www.excelforum.com/showthrea...threadid=472665

--

Dave Peterson

This formula we use In The DataRefiner Add-in and in EasyFilter

=IF(COUNTIF($A$1:$A$16,A2)=1,"Unique",IF(COUNTIF($A$1:$A$16,A2)=0,"Empty","Duplicate("& COUNTIF($A$1:A2,A2)&")"))



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:43417057.847D5BEB@verizonXSPAM.net...
> You could use a helper column of formulas. But that formula depends on what you
> mean by unique.
>
> Do you want just the values that occur only once--or do you want each value when
> it occurs the first time.
>
> If your data is in A2:A999
> You could insert a new column B and use a formula like:
>
> =countif($a$2:$a$999,a2)
> and drag down
>
> And filter to show just the 1's (only the values that appear once will appear).
>
> or...
>
> =countif($a$2:a2,a2)
> and drag down.
>
> Then filter to show just the 1's. The visible rows will contain just the first
> time that value appeared (no matter if it showed up once or hundreds of times).
>
> You may want to read some of Chip Pearson's techniques for dealing with
> duplicates:
> http://www.cpearson.com/excel/duplicat.htm
>
> vrijbergen wrote:
>>
>> Hi,
>>
>> I have the following problem: I want to filter my excel sheet for
>> unique data. I have many columns with text-numbers (example ABC145-59)
>> and I want to filter in such a way that only the unique values are
>> remaining. I cannot use the data-filter function of Excel.
>>
>> How can I do this?
>>
>> Thanks Michiel
>>
>> --
>> vrijbergen
>> ------------------------------------------------------------------------
>> vrijbergen's Profile: http://www.excelforum.com/member.ph...fo&userid=27757
>> View this thread: http://www.excelforum.com/showthrea...threadid=472665
>
> --
>
> Dave Peterson

Why can you not use the data filter method? What about the Advanced Filter
| Unique method?

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"vrijbergen" <vrijbergen.1wc5ua_1128359104.2786@excelforum-nospam.com> wrote
in message news:vrijbergen.1wc5ua_1128359104.2786@excelforum-nospam.com...
>
> Hi,
>
> I have the following problem: I want to filter my excel sheet for
> unique data. I have many columns with text-numbers (example ABC145-59)
> and I want to filter in such a way that only the unique values are
> remaining. I cannot use the data-filter function of Excel.
>
> How can I do this?
>
> Thanks Michiel
>
>
> --
> vrijbergen
> ------------------------------------------------------------------------
> vrijbergen's Profile:
> http://www.excelforum.com/member.ph...fo&userid=27757
> View this thread: http://www.excelforum.com/showthrea...threadid=472665
>