"list unique" instructions fr xldynamic site doent work
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 |
"list unique" instructions fr xldynamic site doent work
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 |
"list unique" instructions fr xldynamic site doent work
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 |
"list unique" instructions fr xldynamic site doent work
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 |
"list unique" instructions fr xldynamic site doent work
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 --- |
All times are GMT +1. The time now is 01:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com