Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on last two digits of data
A sorting question.
I have a sheet of data A12:BC77. I would like to sort this on items in column A. The problem is I need to order it by the last two digits of the data in Column A. So say ZWE11 will come before AA77 and QW22 will come before BB55 and so on. There is probably a simple answer but it is missing me at the moment. All I can think of is splitting out the last two digits into a new column and sorting on that, then deleting that column once sorted but seems very messy. Any advice will be very gratefully received -- with kind regards Spike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on last two digits of data
Hi Spike
If I had to write a macro for this task, it would work just as you suggest to do manually. Why not use column BD as helper column. Enter the formula below in BD12 and copy it down to BD77. =Right(A12,2) Now select the entire table, sort on column BD, and hide the column for future use. (You can sort on column BD even when it is hidden) Hopes this helps. .... Per On 5 Feb., 18:51, Spike wrote: A sorting question. * I have a sheet of data A12:BC77. *I would like to sort this on items in column A. *The problem is I need to order it by the last two digits of the data in Column A. *So say ZWE11 will come before AA77 and QW22 will come before BB55 and so on. *There is probably a simple answer but it is missing me at the moment. All I can think of is splitting out the last two digits into a new column and sorting on that, then deleting that column once sorted but seems very messy. Any advice will be very gratefully received -- with kind regards Spike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on last two digits of data
I would do what you said. Insert a column before Col. A. Then put this
formula in A12, "=RIGHT(B12, 2)". Copy the formula down to row 77. This will put the last 2 characters of data from Col. B in Col. A. Then hide the column. Now you can sort by Column A. This would be much easier than doing it by code. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Spike" wrote: A sorting question. I have a sheet of data A12:BC77. I would like to sort this on items in column A. The problem is I need to order it by the last two digits of the data in Column A. So say ZWE11 will come before AA77 and QW22 will come before BB55 and so on. There is probably a simple answer but it is missing me at the moment. All I can think of is splitting out the last two digits into a new column and sorting on that, then deleting that column once sorted but seems very messy. Any advice will be very gratefully received -- with kind regards Spike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on last two digits of data
Thank you, that is what i have done in code but thought there must be a
neater way of doing it -- with kind regards Spike "Ryan H" wrote: I would do what you said. Insert a column before Col. A. Then put this formula in A12, "=RIGHT(B12, 2)". Copy the formula down to row 77. This will put the last 2 characters of data from Col. B in Col. A. Then hide the column. Now you can sort by Column A. This would be much easier than doing it by code. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Spike" wrote: A sorting question. I have a sheet of data A12:BC77. I would like to sort this on items in column A. The problem is I need to order it by the last two digits of the data in Column A. So say ZWE11 will come before AA77 and QW22 will come before BB55 and so on. There is probably a simple answer but it is missing me at the moment. All I can think of is splitting out the last two digits into a new column and sorting on that, then deleting that column once sorted but seems very messy. Any advice will be very gratefully received -- with kind regards Spike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I sort a list of numbers by the last 4 digits? | Excel Discussion (Misc queries) | |||
sort a cell by last two digits | Excel Discussion (Misc queries) | |||
How do I sort the last 3 digits first and then the first 4 digit?. | Excel Worksheet Functions | |||
Sort by last 2 digits | Excel Discussion (Misc queries) | |||
Sort by last 2 digits | Excel Worksheet Functions |