sorting text using formulas

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I would like to sort a list of text that is created using
lookup functions. The list is dynamic. I need to use
formulas. Can anyone help?

Thanks,
Bill
 
Hi
if your data is in A1:A100 try the following array formula
in B1 (entered with CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$100,MATCH(SMALL(COUNTIF
($A$1:$A$100,"<"&$A$1:$A$100)
+COUNT($A$1:$A$100)*ISTEXT($A$1:$A$100),ROW()-ROW($B$1)+1),
COUNTIF($A$1:$A$100,"<"&$A$1:$A$100)+COUNT($A$1:$A$100)
*ISTEXT($A$1:$A$100),0))
 
If your list will contain *only* text, try this array formula:

=INDEX($A$1:$A$10,MATCH(SMALL(COUNTIF($A$1:$A$10,"<"&$A$1:$A$10),ROW()-ROW($
E$1)+1),COUNTIF($A$1:$A$10,"<"&$A$1:$A$10),0))

Must be entered with CSE (<Ctrl> <Shift> <Enter>)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I would like to sort a list of text that is created using
lookup functions. The list is dynamic. I need to use
formulas. Can anyone help?

Thanks,
Bill
 
Bill wrote...
I would like to sort a list of text that is created using lookup
functions. The list is dynamic. I need to use formulas. . . .
Click to expand...

If your derived list were in A1:A24 and could include text and numbers
and you want to display numbers before text, then enter the followin
*array* formulas.

B1:
=INDEX($A$1:$A$24,MATCH(0,MMULT(--($A$1:$A$24
TRANSPOSE($A$1:$A$24)),ROW($A$1:$A$24)^0),0))
B2:
=IF(SUMPRODUCT(--($A$1:$A$24=B1))
MATCH(COUNTA(B$1:B1),MMULT(--($A$1:$A$24
TRANSPOSE($A$1:$A$24)),ROW($A$1:$A$24)^0),0)))
Click to expand...

Select B2 and fill down into B3:B24. This will also sort any TRUE o
FALSE values in your derived list to the bottom of the list.

If your derived list contained only text, then you could use the arra
formulas

B1:
=INDEX($A$1:$A$24,MATCH(0,
COUNTIF($A$1:$A$24,"<"&$A$1:$A$24),0))

B2:
=IF(COUNTIF($A$1:$A$24,B1)>COUNTIF(B$1:B1,B1),B1,
INDEX($A$1:$A$24,MATCH(COUNTIF($A$1:$A$24,"<="&B1),
COUNTIF($A$1:$A$24,"<"&$A$1:$A$24),0)))

again selecting B2 and filling down into B3:B24. It's much easier i
your derived list contains all numbers.

B1 [not an array formula]:
=MIN($A$1:$A$24)

B2 [array formula]:
=IF(COUNTIF($A$1:$A$24,B1)>COUNTIF(B$1:B1,B1),B1,
MIN(IF($A$1:$A$24>B1,$A$1:$A$24)))

again selecting B2 and filling down into B3:B24
 
Frank Kabel wrote...
if your data is in A1:A100 try the following array formula in B1 ...
=INDEX($A$1:$A$100,MATCH(SMALL(COUNTIF($A$1:$A$100,
"<"&$A$1:$A$100)+COUNT($A$1:$A$100)
*ISTEXT($A$1:$A$100),ROW()-ROW($B$1)+1),
COUNTIF($A$1:$A$100,"<"&$A$1:$A$100)+
COUNT($A$1:$A$100)*ISTEXT($A$1:$A$100),0))
Click to expand...
...

Your formula works if there are only numbers and text in A1:A100, bu
it chokes on booleans. For example, if A1:A10 contained

1
2
3
4
A
B
C
D
TRUE
FALSE

your formula modified to

B1:
=INDEX($A$1:$A$10,MATCH(SMALL(COUNTIF($A$1:$A$10,
"<"&$A$1:$A$10)+COUNT($A$1:$A$10)*ISTEXT($A$1:$A$10),
ROW()-ROW($B$1)+1),COUNTIF($A$1:$A$10,"<"&$A$1:$A$10)
+COUNT($A$1:$A$10)*ISTEXT($A$1:$A$10),0))

returns

1
1
2
2
3
4
A
B
C
 
Frank Kabel wrote...
[...]
Your formula works if there are only numbers and text in A1:A100, but
it chokes on booleans. For example, if A1:A10 contained
Click to expand...

Hi Harlan
and I thought I had tested it :-) Thanks for pointing this out (never
have used booleans in my test cases) I just saw your post. Without
testing it I'd assume your first formula will work on booleans?

Frank
 
Frank Kabel wrote...
. . . I just saw your post. Without testing it I'd assume your first
formula will work on booleans?
Click to expand...

It does. It chokes on error values blanks, but since the OP's sourc
range is formula-generated, I decided I could ignore blanks. As fo
error values, I leave that to you or others.

Me, I'd use a Calculate event handler to copy the source rangee t
where it should appear sorted, then sort the copy. Maybe I'd store th
previous source range's .Value in a Static array to check if I neede
to do anything
 
Stop being so prolific Harlan!<g>
I'm having a hard time keeping up with copying all your formulas to my
"SaveIt" file!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Bill wrote...
I would like to sort a list of text that is created using lookup
functions. The list is dynamic. I need to use formulas. . . .
Click to expand...

If your derived list were in A1:A24 and could include text and numbers,
and you want to display numbers before text, then enter the following
*array* formulas.

B1:
=INDEX($A$1:$A$24,MATCH(0,MMULT(--($A$1:$A$24
TRANSPOSE($A$1:$A$24)),ROW($A$1:$A$24)^0),0))
B2:
=IF(SUMPRODUCT(--($A$1:$A$24=B1))
MATCH(COUNTA(B$1:B1),MMULT(--($A$1:$A$24
TRANSPOSE($A$1:$A$24)),ROW($A$1:$A$24)^0),0)))
Click to expand...

Select B2 and fill down into B3:B24. This will also sort any TRUE or
FALSE values in your derived list to the bottom of the list.

If your derived list contained only text, then you could use the array
formulas

B1:
=INDEX($A$1:$A$24,MATCH(0,
COUNTIF($A$1:$A$24,"<"&$A$1:$A$24),0))

B2:
=IF(COUNTIF($A$1:$A$24,B1)>COUNTIF(B$1:B1,B1),B1,
INDEX($A$1:$A$24,MATCH(COUNTIF($A$1:$A$24,"<="&B1),
COUNTIF($A$1:$A$24,"<"&$A$1:$A$24),0)))

again selecting B2 and filling down into B3:B24. It's much easier if
your derived list contains all numbers.

B1 [not an array formula]:
=MIN($A$1:$A$24)

B2 [array formula]:
=IF(COUNTIF($A$1:$A$24,B1)>COUNTIF(B$1:B1,B1),B1,
MIN(IF($A$1:$A$24>B1,$A$1:$A$24)))

again selecting B2 and filling down into B3:B24.
 
Back
Top