Summarize range

[willemeulen]

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

 

[Domenic]

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$22)<=$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$22
)),"")

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($D22)<=$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(
$D22)),"")

 

[Teethless mama]

In B1: =SMALL(Data,1)
In C1: =IF(MIN(IF(Data>B1,Data))=0,"",MIN(IF(Data>B1,Data)))
ctrl+shift+enter, not just enter. Copy C1 across to other columns

 

[willemeulen]

This Ctrl+Shift+Enter thing, array or something they call it. I read in
some of the forums you should avoid it when possible as it has some
disadvantages.

Once done does it work or should the user repeat it after the sheet has
been modified? I would like to avoid that as I'm writing the sheet for
other people to use, and they are complete with Excel.

 

[Domenic]

This Ctrl+Shift+Enter thing, array or something they call it. I read in
some of the forums you should avoid it when possible as it has some
disadvantages.

In general, array formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, take longer to calculate. If the range is
relatively small and the worksheet doesn't contain many of them, then it
likely won't be an issue. However, if the range is large and the
formula is copied to many cells, it can really slow down the worksheet.

Once done does it work or should the user repeat it after the sheet has
been modified?

The only time an array formula needs to be re-confirmed with CSE is
after modifying the formula itself.

I would like to avoid that as I'm writing the sheet for
other people to use, and they are complete with Excel.

Assuming that A2:A9 contains the data, try...

C2:

=SMALL(A2:A9,1)

C3, copied down until the formula returns #NUM!:

=SMALL($A$2:$A$9,COUNTIF($A$2:$A$9,"<="&C2)+1)