If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sort Formula for TEXT
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 -----Original Message----- 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 "Aaron" wrote in message ... 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. . |
#2
|
|||
|
|||
Sort Formula for TEXT
You've lost us. Why can't you simply sort on Column F?
-- Regards, Fred Please reply to newsgroup, not e-mail "Aaron" wrote in message ... 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 -----Original Message----- 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 "Aaron" wrote in message ... 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. . |
Thread Tools | |
Display Modes | |
|
|