Sorting array function explanation please
 

I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range
named AList.
The original formula was supplied by Harlan Grove in Jun 2004...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()-
ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0))

This array formula works perfectly; however, I am having trouble
understanding how it works.

The SMALL function’s first argument, COUNTIF(AList,"<"&AList)
+COUNT(AList), has to return an array.
This is the bit I can’t understand.
Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList)
manages to return an array for the SMALL function’s first argument?

Ken Johnson

Sorting array function explanation please
 

Ken Johnson wrote:
I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range
named AList.
The original formula was supplied by Harlan Grove in Jun 2004...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()-
ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0))

This array formula works perfectly; however, I am having trouble
understanding how it works.

The SMALL function’s first argument, COUNTIF(AList,"<"&AList)
+COUNT(AList), has to return an array.
This is the bit I can’t understand.
Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList)
manages to return an array for the SMALL function’s first argument?

Ken Johnson

Hi Ken,

Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array
of scalars is created here. That is the nature of array formulae.

You can test this by placing the cursor on a cell with the formula and
using the formula evaluator under Tools | Formula Auditing | Evaluate
Formula. Try it on a fairly small sample (3-4 rows) of data.

FWIW SMALL is just as happy to accept a single constant for its first
argument, even though it seems pointless to do so:

=SMALL(5,1) returns 5

Cool find, by the way!

Hope this helps.

Sorting array function explanation please
 

Hi smartin,

Thanks for your suggestion!
I've not used the Formula evaluator before. It clearly shows the
arrays of values that the formula uses. Perfect!

Cool find, by the way!
Cooler still, in the same post Harlan also supplied a version that
will sort a mixture of text and numbers...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)
+COUNT(AList)*ISTEXT(AList),ROW()-ROW($E$1)+1),COUNTIF(AList,"<"&AList)
+COUNT(AList)*ISTEXT(AList),0))

where the formula starts in E1. Also, just swapping the "<"s with ""s
changes the sort to descending.

Thanks again for your help.

Ken Johnson


smartin wrote:
Ken Johnson wrote:
I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range
named AList.
The original formula was supplied by Harlan Grove in Jun 2004...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()-
ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0))

This array formula works perfectly; however, I am having trouble
understanding how it works.

The SMALL function's first argument, COUNTIF(AList,"<"&AList)
+COUNT(AList), has to return an array.
This is the bit I can't understand.
Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList)
manages to return an array for the SMALL function�s first argument?

Ken Johnson

Hi Ken,

Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array
of scalars is created here. That is the nature of array formulae.

You can test this by placing the cursor on a cell with the formula and
using the formula evaluator under Tools | Formula Auditing | Evaluate
Formula. Try it on a fairly small sample (3-4 rows) of data.

FWIW SMALL is just as happy to accept a single constant for its first
argument, even though it seems pointless to do so:

=SMALL(5,1) returns 5

Cool find, by the way!

Hope this helps.

Sorting array function explanation please
 

Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array of
scalars is created here. That is the nature of array formulae.

That is correct.

COUNTIF(ALIST,"<"&ALIST)+COUNT(AList)

If AList refers to A1:A5 then this is how the array breaks down:

COUNTIF(A1:A5,"<"&A1)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A2)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A3)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A4)+COUNT(A1:A5)
COUNTIF(A1:A5,"<"&A5)+COUNT(A1:A5)

--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
Ken Johnson wrote:
I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range
named AList.
The original formula was supplied by Harlan Grove in Jun 2004...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()-
ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0))

This array formula works perfectly; however, I am having trouble
understanding how it works.

The SMALL function’s first argument, COUNTIF(AList,"<"&AList)
+COUNT(AList), has to return an array.
This is the bit I can’t understand.
Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList)
manages to return an array for the SMALL function’s first argument?

Ken Johnson

Hi Ken,

Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array of
scalars is created here. That is the nature of array formulae.

You can test this by placing the cursor on a cell with the formula and
using the formula evaluator under Tools | Formula Auditing | Evaluate
Formula. Try it on a fairly small sample (3-4 rows) of data.

FWIW SMALL is just as happy to accept a single constant for its first
argument, even though it seems pointless to do so:

=SMALL(5,1) returns 5

Cool find, by the way!

Hope this helps.

Sorting array function explanation please
 

On Aug 23, 12:15 pm, Ken Johnson wrote:
Hi smartin,

Thanks for your suggestion!
I've not used the Formula evaluator before. It clearly shows the
arrays of values that the formula uses. Perfect!

Cool find, by the way!

Cooler still, in the same post Harlan also supplied a version that
will sort a mixture of text and numbers...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)
+COUNT(AList)*ISTEXT(AList),ROW()-ROW($E$1)+1),COUNTIF(AList,"<"&AList)
+COUNT(AList)*ISTEXT(AList),0))

where the formula starts in E1. Also, just swapping the "<"s with ""s
changes the sort to descending.

Thanks again for your help.

Ken Johnson

smartin wrote:
Ken Johnson wrote:
I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range
named AList.
The original formula was supplied by Harlan Grove in Jun 2004...

=INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()-
ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0))

This array formula works perfectly; however, I am having trouble
understanding how it works.

The SMALL function's first argument, COUNTIF(AList,"<"&AList)
+COUNT(AList), has to return an array.
This is the bit I can't understand.
Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList)
manages to return an array for the SMALL function s first argument?

Ken Johnson

Hi Ken,

Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array
of scalars is created here. That is the nature of array formulae.

You can test this by placing the cursor on a cell with the formula and
using the formula evaluator under Tools | Formula Auditing | Evaluate
Formula. Try it on a fairly small sample (3-4 rows) of data.

FWIW SMALL is just as happy to accept a single constant for its first
argument, even though it seems pointless to do so:

=SMALL(5,1) returns 5

Cool find, by the way!

Hope this helps.

Tell a lie...
You just change SMALL to LARGE and leave the "<"s alone to change to
descending sort.

Ken Johnson