A spreadsheet has 1,048,576 rows. I wanted to simply delete all unused rows. When I tried this using F5, I saw the hourglass, came back 20 minutes later, and things seemed to be going in repeating sequences. Never did work.
Weirdness with 365 version of excel, if you have pasted Values Transposed into your excel sheet, the F5 > Go To Special > Blanks method returns an error of No Cells Were Found. Strange.
We are trying to sort rows in numerical order (we have about 70 rows with these type numbers below). We know that's doable in numerical order, however, the numbers are unusual. Her is an example of a column and each number is in a row: 2-6-0 2-10-2 2-6-2 4-8-2 4-4-2 2-8-2 2-8-4 2-6-4 2-8-0 0-6-0 2-10-0 The problem: The focus is on getting the middle number(s) all grouped together and then sorted in order with the first digit (and last digit) then being numerically sorted. We are trying to sort them like this in the column as an example: 4-4-2 0-6-0 2-6-0 2-6-2 2-6-4 2-8-0 2-8-2 2-8-4 4-8-2 2-10-0 2-10-2 Normal numerical sorting would not group these correctly like shown because it would use the first number as the sorting point.These numbers by the way are railroad steam engine designations for their wheel arrangements, for which I have about 70 vintage brass models of these in HO scale and I need to sort them correctly to find them easily on a spread sheet. There are 12 columns in our spread sheet (one column with the numbers above) and the other columns with other data like: manufacturer, whether factory painted, condition, w/original box, etc). Thanks and hoping there is a way to sort them. Thanks. Philip
~~~~~ I did this in OpenOffice which is free but imagine it works similar in Excel which I use too, just not currently as much. I just already have been doing something similar in OO so I just used it. I'm sure the formulas or similar should likely work in Excel or do it in OO then open the sheet in Excel after. You've likely manually or otherwise done yours, but maybe it'll help you in the future or someone else, idk. ~~~~~ I filled columns A,B,C,D,E as test example: 2-6-0 fdghfd fdghfd fdghfd fdghfd 2-10-2 fdghfd fdghfd fdghfd fdghfd 2-6-2 fdghfd fdghfd fdghfd fdghfd 4-8-2 fdghfd fdghfd fdghfd fdghfd 4-4-2 fdghfd fdghfd fdghfd fdghfd 2-8-2 fdghfd fdghfd fdghfd fdghfd 2-8-4 fdghfd fdghfd fdghfd fdghfd 2-6-4 fdghfd fdghfd fdghfd fdghfd 2-8-0 fdghfd fdghfd fdghfd fdghfd 0-6-0 fdghfd fdghfd fdghfd fdghfd 2-10-0 fdghfd fdghfd fdghfd fdghfd ~~~~ Put the Formulas below in Columns after the ones you're using. All prior columns must not be empty just fill them with a space if you have empty columns to the left of these [in my example I have fdghfd for what I imagine is your other data you mentioned]: Column #1 after your work [say column F as example] This chops front & end 2 characters leaving the middle number 4,6,8 or 10. =RIGHT(LEFT(A1;LEN(A1)-2);LEN(LEFT(A1;LEN(A1)-2))-2) Column #2 after your work [say G] Just gets last 1 character =RIGHT((A1);1) Column #3 after your work [say H] Just gets first 1 character =LEFT((A1);1) Column I [making it where we can convert to numbers and sort, this is converting column F] =Value(F1) Column J [making it where we can convert to numbers and sort, this is converting column G] =Value(G1) Column K [making it where we can convert to numbers and sort, this is converting column H] =Value(H1) Highlight those columns and format cells as numbers [Columns I, J & K] Copy those columns and paste as special [now they are number][Columns I, J & K] Highlight all Columns A to K Top Row to Bottom and Sort by I ascending Then J ascending [do not sort by K yet] Now just Highlight rows with the middle number ALL the same and sort by K ascending [1st middle number 4(if was more than 1), then 6 then 8 then 10] Now they are sorted the way you showed 4-4-2 fdghfd fdghfd fdghfd fdghfd 4 2 4 4 2 4 0-6-0 fdghfd fdghfd fdghfd fdghfd 6 0 0 6 0 0 2-6-0 fdghfd fdghfd fdghfd fdghfd 6 0 2 6 0 2 2-6-2 fdghfd fdghfd fdghfd fdghfd 6 2 2 6 2 2 2-6-4 fdghfd fdghfd fdghfd fdghfd 6 4 2 6 4 2 2-8-0 fdghfd fdghfd fdghfd fdghfd 8 0 2 8 0 2 2-8-2 fdghfd fdghfd fdghfd fdghfd 8 2 2 8 2 2 2-8-4 fdghfd fdghfd fdghfd fdghfd 8 4 2 8 4 2 4-8-2 fdghfd fdghfd fdghfd fdghfd 8 2 4 8 2 4 2-10-0 fdghfd fdghfd fdghfd fdghfd 10 0 2 10 0 2 2-10-2 fdghfd fdghfd fdghfd fdghfd 10 2 2 10 2 2
