Sort Formula

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Is there a built in formula in Excel for Sort? For
example, I would like to have B1:B10 returned as the
sorted (alphabet) result of A1:A10. I know I could create
a marco for this, but I am hoping a formula (or complex
multiple formulas) would solve this issue. Thanks in
advance.
 
Aaron,

If you want to do that using formulas, then you need to add 2 helper
columns to your original table - the first will return the rank of the
value of the second, which must contain some formula that returns a
number or other value that you can sort on. An example will help.

Suppose you have

10
6
5
11

Insert a new column A, and in C1, use the formula

= B1 + ROW()*.000000001

This formula use row() as a tie-breaker.

In A1, use the formula

=RANK(C1,$C$1:$C$4)

and copy down to A4.

Now to make your auto-sorting table.

In cell F1, use the formula
=VLOOKUP(ROW(),$A$1:$B$4,2,FALSE)
and copy down for a total of 4 rows.

If your table doesn't start on row 1, then you need to use something
like this, where your table starts in cell F11:
=VLOOKUP(ROW()-ROW($F10),$A$1:$B$4,2,FALSE)

And as the values in your data table change, your resulting table will
re-sort automatically.

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie,

This is working great with numbers. However, I'm trying
to sort a list of names (TEXT). I have tried to use
the "code" function to determine the letter code, and got
the list working to a point that it will sort by the first
letter of the name. The tie breaker ceased the further
sorting of the next letter and so on... Please see
example...


Col.A Col.B Col.C Col.D Col.E Col.F
3 USA 85 85 CAR
4 UK 85 85 CANADA
5 USA 85 85 USA
1 CAR 67 67 UK
2 CANADA 67 67.00000001 USA

With A1
=RANK(D1,$D$1:$D$5,1)

B1
Data to be sorted

C1
=IF(ISERROR(CODE(LEFT(B1,1))),"",CODE(LEFT(B1,1)))

D1
=C1+ROW()*0.000000001

F1
=VLOOKUP(ROW(),$A$1:$B$5,2,FALSE)

And everything copied down to ROW 5.


Please advise if it is possible to further sort the name
so then the outcome in Col.F would be:

CANADA
CAR
UK
USA
USA

Your assistance is very much appreciated.

Thanks again,
Aaron
 
Is there a built in formula in Excel for Sort? For
example, I would like to have B1:B10 returned as the
sorted (alphabet) result of A1:A10. I know I could create
a marco for this, but I am hoping a formula (or complex
multiple formulas) would solve this issue. Thanks in
advance.
Click to expand...

First off, if all cells in A1:A10 hold distinct values, there's no need for
tie-breaking. If there were duplicate values in A1:A10, and B1:B10 were only to
hold the sorted values from A1:A10, there's still no need for tie-breaking.

Enter the following array formula in 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))

Then double click on the Fill Handle, the square in the bottom right corner of
the border around the active cell. This sorts in ascending order, numbers before
text. To sort in descending order, text before numbers, replace the SMALL calls
with LARGE calls in the formula above.

If A1:A10 contained only numbers or only text, this could be reduced to

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

No intermediate ancillary calculations in other cells needed.
 
Hi Harlan,

Thanks a million!! Works like a charm!!

Regards,
Aaron


-----Original Message-----
...

First off, if all cells in A1:A10 hold distinct values, there's no need for
tie-breaking. If there were duplicate values in A1:A10, and B1:B10 were only to
hold the sorted values from A1:A10, there's still no need for tie-breaking.

Enter the following array formula in B1.

=INDEX($A$1:$A$10,MATCH(SMALL(COUNTIF ($A$1:$A$10,"<"&$A$1:$A$10)
*ISTEXT($A$1:$A$10),0))

Then double click on the Fill Handle, the square in the bottom right corner of
the border around the active cell. This sorts in
Click to expand...
ascending order, numbers before
 
FWIW,
Can't overshadow the text / number formulas, but this little one works great
for numbers *only*:

Ascending,
=SMALL($A$1:$A$100,ROW())

Descending,
=LARGE($A$1:$A$100,ROW())
--


Regards,

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


Is there a built in formula in Excel for Sort? For
example, I would like to have B1:B10 returned as the
sorted (alphabet) result of A1:A10. I know I could create
a marco for this, but I am hoping a formula (or complex
multiple formulas) would solve this issue. Thanks in
advance.
Click to expand...

First off, if all cells in A1:A10 hold distinct values, there's no need for
tie-breaking. If there were duplicate values in A1:A10, and B1:B10 were only
to
hold the sorted values from A1:A10, there's still no need for tie-breaking.

Enter the following array formula in 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))

Then double click on the Fill Handle, the square in the bottom right corner
of
the border around the active cell. This sorts in ascending order, numbers
before
text. To sort in descending order, text before numbers, replace the SMALL
calls
with LARGE calls in the formula above.

If A1:A10 contained only numbers or only text, this could be reduced to

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

No intermediate ancillary calculations in other cells needed.
 
Back
Top