Summarize range
 

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


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97296

Summarize range
 

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

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

--
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

Summarize range
 

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



"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


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97296

Summarize range
 

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 :confused: with Excel.


--
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=97296

Summarize range
 

In article ,
willemeulen wrote:

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 :confused: 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)

--
Domenic
http://www.xl-central.com