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)),RO W(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.

--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Khoshravan" wrote:
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)),RO W(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.

--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

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

"Khoshravan" wrote:

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)),RO W(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.

--
Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

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.
--
Max
Singapore

Hi Max,

Typo...? Should maybe be

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

Regards
Martin

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()) ))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---