Dear Teethless,
I have been playing with this formula for a while now just to make sure my
issue is not user related! There are cells containing zero in my data range,
and this seems to cause a problem.
a
b
0
d
e
f
g
h
0

will return the unique list

a
b
0
d
e
f
g


I won't see h until i enter something in below it, other than zero. It seems
once a zero has been encountered once, then it causes problems for data
coming immediately before the next zero, ie. the entry won't appear in the
unique list.
Can I accomodate these zeros and the effect they seem to have on the unique
filtering?

Many thanks

Martina


"Teethless mama" wrote:

No Advanced Filter, No array formula, No problem...

Assume your data in column A with a header in row1. Defined name range
"data" no quotes

In B2: =IF(COUNTIF($A$2:A2,A2)=1,ROWS($1:1),"")
copy down. Defined name range in columnB "helper" of course no quotes

In C2:
=IF(COUNT(helper)=(ROWS($1:1)),INDEX(data,SMALL(h elper,ROWS($1:1))),"")
copy down





"jc132568" wrote:

Sorry, I want the list to be created without user intervention. Can I use
something other than an array formula as it slows excel down.
regards
Martina

"Teethless mama" wrote:

Data Filter Advanced Filter select either copy to another location or
Filter the list, in place select your list range Criteria range "leave it
blank" check on unique records only


"jc132568" wrote:

Dear experts,
I have a long list of student numbers (numbers occurring more than once). I
want to set up a formula which automatically filters for unique entries. I
used an array formula which I got on this site and it works beautifully
however it does slow down my spreadsheet. Can I filter a range A1:A500 using
database functions or a pivot table? I have read that these possibly won't
slow the system down so much.
kind regards
Martina