Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha sorting (with a twist...?)
Hi, I've followed MS instructions for alpha sorting (e.g. helper column w/
=text(e5,"###") ) to no help. I think that works only when the value in the column begins with a number. My data can begin w/ a number or alpha character. Here's an example of my data: 1 2 3 4 A1 A10 5 6 7 8 A11 A12 A2 A3 A4 A5 9 10 11 12 A6 A7 A8 A9 B1 B10 B11 B12 B2 B3 B4 B5 B6 B7 B8 B9 And here's how I want it sorted... 1 2 3 4 5 6 7 8 9 10 11 12 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 B1 B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B12 But it keeps coming out like this: 1 2 3 4 5 6 7 8 9 10 11 12 A1 A10 A11 A12 A2 A3 A4 A5 A6 A7 A8 A9 B1 B10 B11 B12 B2 B3 B4 B5 B6 B7 B8 B9 This is the second column of a three-column sort. Suggestions? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha sorting (with a twist...?)
How do you want it to come out on sorting?
Do you want : A1, A2...... 1,2,3..... Let's assume the first item is in cell A1 In B1 enter =IF(ISNUMBER(A1),"ZZZ"&A1,A1) and copy down the column Select all the B values; use Copy ; with them still selected use Paste Special with Values specified: Now B has values not formulas and you can sort using it best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jason F." <Jason wrote in message ... Hi, I've followed MS instructions for alpha sorting (e.g. helper column w/ =text(e5,"###") ) to no help. I think that works only when the value in the column begins with a number. My data can begin w/ a number or alpha character. Here's an example of my data: 1 2 3 4 A1 A10 5 6 7 8 A11 A12 A2 A3 A4 A5 9 10 11 12 A6 A7 A8 A9 B1 B10 B11 B12 B2 B3 B4 B5 B6 B7 B8 B9 And here's how I want it sorted... 1 2 3 4 5 6 7 8 9 10 11 12 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 B1 B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B12 But it keeps coming out like this: 1 2 3 4 5 6 7 8 9 10 11 12 A1 A10 A11 A12 A2 A3 A4 A5 A6 A7 A8 A9 B1 B10 B11 B12 B2 B3 B4 B5 B6 B7 B8 B9 This is the second column of a three-column sort. Suggestions? Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha sorting (with a twist...?)
your suggestion resulted in this:
ZZZ1 ZZZ10 ZZZ11 ZZZ12 ZZZ13 ZZZ14 ZZZ15 ZZZ16 ZZZ17 ZZZ18 ZZZ19 ZZZ2 ZZZ20 ZZZ21 ...... ZZZ8 ZZZ9 A1 A10 A11 A12 A2 A3 A4 A5 A6 A7 A8 A9 B1 B10 B11 B12 B2 B3 B4 ....... The problem is that the A10, A11 and A12 are being sorted after A1, instead of after A9. BTW, this is the result if the column is formatted as text or general. thanks, "Bernard Liengme" wrote: How do you want it to come out on sorting? Do you want : A1, A2...... 1,2,3..... Let's assume the first item is in cell A1 In B1 enter =IF(ISNUMBER(A1),"ZZZ"&A1,A1) and copy down the column Select all the B values; use Copy ; with them still selected use Paste Special with Values specified: Now B has values not formulas and you can sort using it best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jason F." <Jason wrote in message ... Hi, I've followed MS instructions for alpha sorting (e.g. helper column w/ =text(e5,"###") ) to no help. I think that works only when the value in the column begins with a number. My data can begin w/ a number or alpha character. Here's an example of my data: 1 2 3 4 <<<SNIP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha sorting (with a twist...?)
... My data can begin w/ a number or alpha character.
Here's an example of my data: 1 2 3 4 A1 A10 5 6 7 8 A11 A12 A2 A3 A4 A5 9 10 11 12 A6 A7 A8 A9 B1 B10 B11 B12 B2 B3 B4 B5 B6 B7 B8 B9 And here's how I want it sorted... 1 2 3 4 5 6 7 8 9 10 11 12 A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 B1 B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B12 The following works for me with Excel 2003. In B1, put this formula and copy it down as far as needed: =IF(ISNUMBER(A1),TEXT(A1,"0000"), LEFT(A1,1)&TEXT(MID(A1,2,99),"0000")) Select columns A and B together and sort by column B, ascending. Then, column A should be in the desired order. Column B can be hidden when not in use to avoid clutter. If the list can change in length, you may not want blank entries to sort at the top. To avoid that, wrap the above formula like this: =IF(ISBLANK(A1),"ZZZZZ", <above formula) and copy down longer than the longest possible list. Modify to suit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Alpha-Numeric Values | Charts and Charting in Excel | |||
Sorting alpha in two columns | Excel Discussion (Misc queries) | |||
Sorting Alpha Numeric | Excel Discussion (Misc queries) | |||
Alpha-Numeric Sorting | Excel Discussion (Misc queries) | |||
Sorting by Date (With a Twist) | Excel Discussion (Misc queries) |