"list unique" instructions fr xldynamic site doent work

[Guest]

I am trying to use the "list unique" instructions given by xldynamic given in
the following link:
http://www.xldynamic.com/source/xld.xlFAQ0021.html
Its instructions are as follows:
A1:A100 are the input data, and we want to find the unique list of these data
B1=IF(COUNTIF($A$1:A1,A1)=1,ROW(A1),"") This givens the row number of
unique entries and works fine
C1=SMALL(IF($B$1:$B$100="","",ROW($B$1:$B$100)),ROW(B1)) an array formula,
and my data it returns 1 for all columns which is strange
D1=IF(ISNUMBER(C1),INDEX(A:A,C1),"")) supposed to give the unique list but
in my case only gives the first entry in all cells.

I think something is wrong in C1 formula.
Any comment is welcomed.

 

[Guest]

I'm not sure why you got the errors -- did you array-enter correctly the
formula in C1, pressing CTRL+SHIFT+ENTER ??

Anyway <g> .. here's a non-array alternative which delivers it using 2 cols

Data is assumed running in A1 down

In B1:
=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))

In C1:
=IF(ROW()>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROW())))
Select B1:C1, copy down to the max expected extent of data in col A. Hide
away col B. Col C returns the list of uniques in col A, with results neatly
bunched at the top.

 

[Guest]

Try This in Column B
=IF(COUNTIF($A$1:$A$100,A1)=COUNTIF(A1:A100,A1),A1," ")

 

[MartinW]

In C1:

=IF(ROW()>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROW())))
Select B1:C1, copy down to the max expected extent of data in col A. Hide
away col B. Col C returns the list of uniques in col A, with results
neatly
bunched at the top.

Hi Max,

Typo...? Should maybe be

In C1:
=IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))

Regards
Martin

 

[Guest]

Typo...? Should maybe be

Yes, it was, Martin. Thanks.

It should read, as you said:

In C1:
=IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))

---