Filter data per function

[vrijbergen]

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

 

[Dave Peterson]

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

 

[Ron de Bruin]

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)&")"))

 

[Zack Barresse]

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