Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers to sort as text
Excel 2000 (or upgrade?)
Is there any way to get Excel to sort numbers (accounting categories) as text, i.e., the sorted output would look like: 5 50 501 5011 5012 5013 51 511 5111 5112 5113 6 I get the correct sorting action if I replace the numbers with, e.g., letters A through J. I don't want to fill in the right-hand digits with zeros, because that would break something. Simply formatting the cells as text doesn't work. I'm using the account numbers, via cell range reference, in a SUMIF formula's critera, that isn't hard coded, but gets the accounting category to subtotal from the data in the cells to be sorted. =FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10),2) I don't want to break the above formula, where RC8 is the cell that contains the account number. C8 contains the accounting category for the individual transaction being posted, which account number is four digits. If the account number has less than four digits, the value is a sum of all transactions that have those first digits in their account number. TIA Fred Holmes |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers to sort as text
You can do it manually as follows. Assume your data is in A1:A10.
Insert a new column B to the right of this and enter in B1 =TEXT(A1,"0") and copy down to B10. Next, select B1:B10 and move the mouse over the right hand selection border so that it turns to a four-pointed arrow. Hold down the RIGHT mouse button, drag the selection one column to the right and then back to the left onto itself and release the right mouse button. In the popup menu that displays, choose "Copy Here As Values Only". With B1:B10 selected, go to the Data menu and choose Sort. If you get a sort warning about data being next to the selection, choose Expand Select and click Sort. In the next Sort dialog, choose Column B as the Sort By setting. Click Sort. You'll get a warning asking whether to treat things that look like numbers as number or to treat them as text. Choose the "treat as text" option and click OK. Now, your numbers will be sorted in the desired order. You can automate this with: Sub AAA() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Range("A1:A10") '<<<< CHANGE SourceRange(1, 2).EntireColumn.Insert Set DestRange = SourceRange(1, 2).Resize(SourceRange.Rows.Count) With DestRange .Formula = "=TEXT(A1,""0"")" .Copy .PasteSpecial xlPasteValues .Sort key1:=DestRange(1, 1), order1:=xlAscending End With End Sub Just change the reference to SourceRange to the appropriate range. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 21 Mar 2009 13:25:56 -0400, Fred Holmes wrote: Excel 2000 (or upgrade?) Is there any way to get Excel to sort numbers (accounting categories) as text, i.e., the sorted output would look like: 5 50 501 5011 5012 5013 51 511 5111 5112 5113 6 I get the correct sorting action if I replace the numbers with, e.g., letters A through J. I don't want to fill in the right-hand digits with zeros, because that would break something. Simply formatting the cells as text doesn't work. I'm using the account numbers, via cell range reference, in a SUMIF formula's critera, that isn't hard coded, but gets the accounting category to subtotal from the data in the cells to be sorted. =FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10),2 ) I don't want to break the above formula, where RC8 is the cell that contains the account number. C8 contains the accounting category for the individual transaction being posted, which account number is four digits. If the account number has less than four digits, the value is a sum of all transactions that have those first digits in their account number. TIA Fred Holmes |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers to sort as text
Many thanks for the instructions. I haven't tried it yet to see what
is actually happening. It appears that you have created macro/VBA code to fill in a separate column of data (Column B) on which to actually sort, rather than sorting on the real data (Column A). There is, of course, always the option to manually create such a column, but it is a hard-coded column B (copy as values only)?? In what I am doing there will be intances of my wanting to change the accounting category on the fly, and have the sort work. I will experiment with this code and see what I can learn from it and how to make it perform. My nirvana would be to sort it either way on user's choice. By having two sorting columns, I guess this provides that capability. Hopefully the artificial sorting column (Column B) can be a hidden Column. I wouldn't want it to display in printouts. It would be nice if the Sort would work on the _displayed_ number, and there were a display formatting code to fill in _trailing_ zeros (rather than leading zeros, for which there is display formatting code) Many thanks for all that you and the other MVPs do. Especially on weekends. Fred Holmes On Sat, 21 Mar 2009 15:02:49 -0500, Chip Pearson wrote: You can do it manually as follows. Assume your data is in A1:A10. Insert a new column B to the right of this and enter in B1 =TEXT(A1,"0") and copy down to B10. Next, select B1:B10 and move the mouse over the right hand selection border so that it turns to a four-pointed arrow. Hold down the RIGHT mouse button, drag the selection one column to the right and then back to the left onto itself and release the right mouse button. In the popup menu that displays, choose "Copy Here As Values Only". With B1:B10 selected, go to the Data menu and choose Sort. If you get a sort warning about data being next to the selection, choose Expand Select and click Sort. In the next Sort dialog, choose Column B as the Sort By setting. Click Sort. You'll get a warning asking whether to treat things that look like numbers as number or to treat them as text. Choose the "treat as text" option and click OK. Now, your numbers will be sorted in the desired order. You can automate this with: Sub AAA() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Range("A1:A10") '<<<< CHANGE SourceRange(1, 2).EntireColumn.Insert Set DestRange = SourceRange(1, 2).Resize(SourceRange.Rows.Count) With DestRange .Formula = "=TEXT(A1,""0"")" .Copy .PasteSpecial xlPasteValues .Sort key1:=DestRange(1, 1), order1:=xlAscending End With End Sub Just change the reference to SourceRange to the appropriate range. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 21 Mar 2009 13:25:56 -0400, Fred Holmes wrote: Excel 2000 (or upgrade?) Is there any way to get Excel to sort numbers (accounting categories) as text, i.e., the sorted output would look like: 5 50 501 5011 5012 5013 51 511 5111 5112 5113 6 I get the correct sorting action if I replace the numbers with, e.g., letters A through J. I don't want to fill in the right-hand digits with zeros, because that would break something. Simply formatting the cells as text doesn't work. I'm using the account numbers, via cell range reference, in a SUMIF formula's critera, that isn't hard coded, but gets the accounting category to subtotal from the data in the cells to be sorted. =FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10), 2) I don't want to break the above formula, where RC8 is the cell that contains the account number. C8 contains the accounting category for the individual transaction being posted, which account number is four digits. If the account number has less than four digits, the value is a sum of all transactions that have those first digits in their account number. TIA Fred Holmes |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers to sort as text
Hi,
Suppose your data is in A1A100 in B1 enter the formula =LEFT(A1,9) copy this down and sort both columns (or these and others) by this new column. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fred Holmes" wrote: Excel 2000 (or upgrade?) Is there any way to get Excel to sort numbers (accounting categories) as text, i.e., the sorted output would look like: 5 50 501 5011 5012 5013 51 511 5111 5112 5113 6 I get the correct sorting action if I replace the numbers with, e.g., letters A through J. I don't want to fill in the right-hand digits with zeros, because that would break something. Simply formatting the cells as text doesn't work. I'm using the account numbers, via cell range reference, in a SUMIF formula's critera, that isn't hard coded, but gets the accounting category to subtotal from the data in the cells to be sorted. =FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10),2) I don't want to break the above formula, where RC8 is the cell that contains the account number. C8 contains the accounting category for the individual transaction being posted, which account number is four digits. If the account number has less than four digits, the value is a sum of all transactions that have those first digits in their account number. TIA Fred Holmes |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers to sort as text
Hello,
If you do not have too many records I suggest to use my UDF GSort with the (S)tring comparison parameter. A worksheet function approach: http://www.sulprobil.com/html/sorting.html (You would need to insert "" & before the second argument of Countif to enforce string comparison.) Regards, Bernd |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbers to sort as text
There is no option to sort things that look like numbers as text
instead of numbers. The only way is to jump through the hoops as I described. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 21 Mar 2009 18:34:57 -0400, Fred Holmes wrote: Many thanks for the instructions. I haven't tried it yet to see what is actually happening. It appears that you have created macro/VBA code to fill in a separate column of data (Column B) on which to actually sort, rather than sorting on the real data (Column A). There is, of course, always the option to manually create such a column, but it is a hard-coded column B (copy as values only)?? In what I am doing there will be intances of my wanting to change the accounting category on the fly, and have the sort work. I will experiment with this code and see what I can learn from it and how to make it perform. My nirvana would be to sort it either way on user's choice. By having two sorting columns, I guess this provides that capability. Hopefully the artificial sorting column (Column B) can be a hidden Column. I wouldn't want it to display in printouts. It would be nice if the Sort would work on the _displayed_ number, and there were a display formatting code to fill in _trailing_ zeros (rather than leading zeros, for which there is display formatting code) Many thanks for all that you and the other MVPs do. Especially on weekends. Fred Holmes On Sat, 21 Mar 2009 15:02:49 -0500, Chip Pearson wrote: You can do it manually as follows. Assume your data is in A1:A10. Insert a new column B to the right of this and enter in B1 =TEXT(A1,"0") and copy down to B10. Next, select B1:B10 and move the mouse over the right hand selection border so that it turns to a four-pointed arrow. Hold down the RIGHT mouse button, drag the selection one column to the right and then back to the left onto itself and release the right mouse button. In the popup menu that displays, choose "Copy Here As Values Only". With B1:B10 selected, go to the Data menu and choose Sort. If you get a sort warning about data being next to the selection, choose Expand Select and click Sort. In the next Sort dialog, choose Column B as the Sort By setting. Click Sort. You'll get a warning asking whether to treat things that look like numbers as number or to treat them as text. Choose the "treat as text" option and click OK. Now, your numbers will be sorted in the desired order. You can automate this with: Sub AAA() Dim SourceRange As Range Dim DestRange As Range Set SourceRange = Range("A1:A10") '<<<< CHANGE SourceRange(1, 2).EntireColumn.Insert Set DestRange = SourceRange(1, 2).Resize(SourceRange.Rows.Count) With DestRange .Formula = "=TEXT(A1,""0"")" .Copy .PasteSpecial xlPasteValues .Sort key1:=DestRange(1, 1), order1:=xlAscending End With End Sub Just change the reference to SourceRange to the appropriate range. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 21 Mar 2009 13:25:56 -0400, Fred Holmes wrote: Excel 2000 (or upgrade?) Is there any way to get Excel to sort numbers (accounting categories) as text, i.e., the sorted output would look like: 5 50 501 5011 5012 5013 51 511 5111 5112 5113 6 I get the correct sorting action if I replace the numbers with, e.g., letters A through J. I don't want to fill in the right-hand digits with zeros, because that would break something. Simply formatting the cells as text doesn't work. I'm using the account numbers, via cell range reference, in a SUMIF formula's critera, that isn't hard coded, but gets the accounting category to subtotal from the data in the cells to be sorted. =FIXED(SUMIF(R4C8:R500C8,RC8&"*",R4C10:R500C10) ,2) I don't want to break the above formula, where RC8 is the cell that contains the account number. C8 contains the accounting category for the individual transaction being posted, which account number is four digits. If the account number has less than four digits, the value is a sum of all transactions that have those first digits in their account number. TIA Fred Holmes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort with text and numbers | Excel Discussion (Misc queries) | |||
Can I ignore numbers and sort only text in an Excel column? | Excel Discussion (Misc queries) | |||
How to sort text as numbers | Excel Discussion (Misc queries) | |||
How can I sort by numbers and not text? | Excel Worksheet Functions | |||
sort text as numbers excell2003 to 2000 | Excel Discussion (Misc queries) |