Automatically sort numbers and letter

[Shegon]

I am trying to set things up so that I can enter information in column A (both numbers and letters) and have a function in column B that displays the information in column A sorted by numbers then letters. For example, I would type in column A:

9
4
A
3
7
CC
2
7
B1
4

Then, Column B would display:

2
3
4
4
7
7
9
A
B1
CC

I can get this to work if it's just numbers with =SMALL($A$1:$A$10;ROW(A1:A2)), but I can't get it to acknowledge letters. I also can't put in something that recognizes letters and converts them to numbers because the letters will be too inconsistent, being made up of capitals, lower case, and numbers. Any help would be greatly appreciated.

[Charlie Young]

You might find karolus' and my add-in function useful

viewtopic.php?f=21&t=56916

I think I have extended that some beyond what's been posted so far, but if i understand what you're after, what's posted should suit your needs.

[Shegon]

Thank you for the input. This does accomplish what I need, unfortunately I need this spreadsheet to work on multiple computers, and it's not practical for me to install this add-in to each computer that might need the spreadsheet.

[ken johnson]

One solution involves using a helper column with a formula that calculates the sorting position of each column A entry.
In the attached doc see column C in sheet named Helper where this formula in C2 has been filled down to C1000...

Code: Select all

=IF(A2="";"";COUNTIF(A$2:A$1000;"<"&A2)+COUNT(A$2:A$1000)*ISTEXT(A$2:A$1000)+1)

This formula in B2 filled down to B1000 uses the column C results to return a sorted copy of column A entries (up to A1000) in column B...

Code: Select all

=IF(ROW(B2)-ROW(B$2)+1>COUNT(C$2:C$1000);"";INDEX(A$2:A$1000;MATCH(SMALL(C$2:C$1000;ROW(B2)-ROW(B$2)+1);C$2:C$1000;0)))

Another solution uses a complex array formula that enables you to do away with the helper column. In the attached doc see column B in sheet named Array Formula where this formula B2 has been filled down to B1000...

Code: Select all

IF(ROW(B2)-ROW(B$2)+1>COUNTA(A$2:A$1000);"";INDEX(A$2:A$1000;MATCH(SMALL(IF(A$2:A$1000="";"";COUNTIF(A$2:A$1000;"<"&A$2:A$1000)+COUNT(A$2:A$1000)*ISTEXT(A$2:A$1000)+1);ROW(B2)-ROW(B$2)+1);IF(A$2:A$1000="";"";COUNTIF(A$2:A$1000;"<"&A$2:A$1000)+COUNT(A$2:A$1000)*ISTEXT(A$2:A$1000)+1);0)))

Array formula must be entered using Ctrl+Shift+Enter PC key combination and the Ctrl key must be held down while dragging or double-clicking the fill handle to copy the array formula into adjacent cells (copy/paste is a better alternative when the array formula needs to be in a large range of cells).

Ken Johnson