Assuming that A2:A9 contains the data, try the following...
1) To return the results in a vertical range of cells...
C2, confirmed with CONTROL+SHIFT+ENTER:
=SUM(IF(FREQUENCY(IF(A2:A9"",MATCH("~"&A2:A9,A2: A9&"",0)),ROW(A2:A9)-RO
W(A2)+1),1))
D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:
=IF(ROWS(D$2
2)=$C$2,SMALL(IF(FREQUENCY(IF($A$2: $A$9"",MATCH("~"&$A$2
:$A$9,$A$2:$A$9&"",0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9),ROWS(D$2
2
)),"")
2) To return the results in a horizontal range of cells...
C2, confirmed with CONTROL+SHIFT+ENTER:
=SUM(IF(FREQUENCY(IF(A2:A9"",MATCH("~"&A2:A9,A2: A9&"",0)),ROW(A2:A9)-RO
W(A2)+1),1))
D2, confirmed with CONTROL+SHIFT+ENTER, and copied across:
=IF(COLUMNS($D2
2)=$C$2,SMALL(IF(FREQUENCY(IF($A $2:$A$9"",MATCH("~"&$
A$2:$A$9,$A$2:$A$9&"",0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9),COLUMNS(
$D2
2)),"")
--
Domenic
http://www.xl-central.com
In article ,
willemeulen wrote:
I want to make an automatic summary of value's present in a certain
column in ascending order.
Example:
Column
8
10
12
10
8
16
16
6
Result:
6 8 10 12 16
In my case the results should be displayed vertical, one value per
column. I know what the maximum amount of different values can be (it
will never exceed 10 different values) and the column which needs to be
filtered is also fixed.
Thanks,
W