Sorting Text Range with Formula

  • Thread starter Thread starter immanuel
  • Start date Start date
I

immanuel

Just wondering if anyone has developed a single multi-cell array formula for
sorting a range containing text. Would anyone know if this has been done?

/i
 
Thanks, Frank,

I already created an array formula to sort text entries but was just
wondering if maybe someone had a better, more efficient way...

/i.
 
Harlan Grove posted this once

=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10&"*"),ROW(A1:A10)-CELL("R
ow",A1:A10)+1),COUNTIF(A1:A10,"<"&A1:A10&"*"),0),{1,2})

array entered in10 cells
 
Hi Peo,

Harlan's formula seems to break with numbers or blanks. The one I wrote is:

=IF(INDEX(Data,RIGHT(TEXT(SMALL(ISTEXT(Data)*ROWS(Data)+(Data="")*ROWS(Data)
*2+ISTEXT(Data)*COUNTIF(Data,"<='"&Data)+ISNONTEXT(Data)*COUNTIF(Data,"<="&D
ata)+(ROW(Data)-MIN(ROW(Data))+1)/100000,ROW(INDIRECT("1:"&ROWS(Data)))),"0.
00000"),5))="","",INDEX(Data,RIGHT(TEXT(SMALL(ISTEXT(Data)*ROWS(Data)+(Data=
"")*ROWS(Data)*2+ISTEXT(Data)*COUNTIF(Data,"<='"&Data)+ISNONTEXT(Data)*COUNT
IF(Data,"<="&Data)+(ROW(Data)-MIN(ROW(Data))+1)/100000,ROW(INDIRECT("1:"&ROW
S(Data)))),"0.00000"),5)))

It sorts entries of mixed data types (text, numbers, numbers as text, and
blanks) and places blanks at the end of the list. (In fact, the order in
which it places the different data types can be controlled quite easily by
modifying the multipliers.)

I expect there are ways to refine the formula to be more efficient,
readable, and elegant. If any of you have suggestions for improvement, I
would sincerely appreciate your sharing them.

Regards,
Immanuel
 
Harlan's formula seems to break with numbers or blanks. The one I wrote is:
Click to expand...

[reformatted slightly]
=IF(INDEX(Data,RIGHT(TEXT(SMALL(ISTEXT(Data)*ROWS(Data)+(Data="")*ROWS(Data)
*2+ISTEXT(Data)*COUNTIF(Data,"<='"&Data)+ISNONTEXT(Data)*COUNTIF(Data,"<="
&Data)+(ROW(Data)-MIN(ROW(Data))+1)/100000,ROW(INDIRECT("1:"&ROWS(Data)))),
"0.00000"),5))="","",INDEX(Data,RIGHT(TEXT(SMALL(ISTEXT(Data)*ROWS(Data)
+(Data="")*ROWS(Data)*2+ISTEXT(Data)*COUNTIF(Data,"<='"&Data)+ISNONTEXT(Data)
*COUNTIF(Data,"<="&Data)+(ROW(Data)-MIN(ROW(Data))+1)/100000,
ROW(INDIRECT("1:"&ROWS(Data)))),"0.00000"),5)))

It sorts entries of mixed data types (text, numbers, numbers as text, and
blanks) and places blanks at the end of the list. (In fact, the order in
which it places the different data types can be controlled quite easily by
modifying the multipliers.)
Click to expand...
...

First a quibble. ROW(INDIRECT("1:"&ROWS(Data))) gives the same 1D array of
integers that ROW(Data)-CELL("Row",Data)+1 does, but the former uses 3 function
calls all nested while the latter uses just two unnested function calls. In
complicated nested formulas, ROW(.)-CELL("Row",.)+1 is always the better idiom
to use.

It's an open qhestion whether numbers as text should be sorted as text or as
numbers. I'd sort them as text. If they should be sorted as numbers, why not
convert them to numbers in the source range?

That said, to sort numbers after text and blanks after both with numbers and
text sorted in ascending order,

=INDEX(IF(ISBLANK(Data),"",Data),MATCH(SMALL(ISBLANK(Data)*2*VERYBIG
+IF(ISNUMBER(Data),VERYBIG+COUNTIF(Data,"<"&Data),COUNTIF(Data,"<"&Data&"*")),
ROW(Data)-CELL("Row",Data)+1),ISBLANK(Data)*2*VERYBIG+IF(ISNUMBER(Data),
VERYBIG+COUNTIF(Data,"<"&Data),COUNTIF(Data,"<"&Data&"*")),0))

where VERYBIG is defined as =1E9 or some other fairly large number.
 
If A1:A4 contains Red,(blank),Apple,Worm
and I try this array formula in B1 and drag to B4,
I get Apple,Apple,Apple,Apple I was hoping for Apple,Red,Worm,(blank).

I'm referencing the range in the formula either named, or like A$1:A$4.

This may be beyond my level, but I'm still curious! <|:)
 
If A1:A4 contains Red,(blank),Apple,Worm
and I try this array formula in B1 and drag to B4,
I get Apple,Apple,Apple,Apple I was hoping for Apple,Red,Worm,(blank).

I'm referencing the range in the formula either named, or like A$1:A$4.
Click to expand...
...

The formula needs to be entered as a *single* array formula spanning a range the
same size as the source data. If I select C1:C4 and enter the array formula,
C1:C4 evaluates to {"Apple";"Red";"Worm";""}.

If you want this as single cell formulas which could be filled down as needed,
then if the topmost result cell were C1, enter the following array formula in C1

=INDEX(IF(ISBLANK(Data),"",Data),MATCH(SMALL(ISBLANK(Data)*2*VERYBIG
+IF(ISNUMBER(Data),VERYBIG+COUNTIF(Data,"<"&Data),COUNTIF(Data,"<"&Data&"*")),
ROW()-ROW($C$1)+1),ISBLANK(Data)*2*VERYBIG+IF(ISNUMBER(Data),VERYBIG
+COUNTIF(Data,"<"&Data),COUNTIFData,"<"&Data&"*")),0))

and fill C1 down as far as needed.
 
Back
Top