Excel sort function

%3CLINGO-SUB%20id%3D%22lingo-sub-2879813%22%20slang%3D%22en-US%22%3EExcel%20sort%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWe%20have%20a%20job%20register%20that%20now%20consists%20of%20over%203000%20rows.%26nbsp%3B%20It%20is%20a%20critical%20part%20of%20our%20business.%26nbsp%3B%20The%20first%20column%20consists%20of%20the%20job%20number%20and%20all%20other%20columns%20are%20relevant%20to%20the%20specific%20job.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20numbering%20system%20we%20have%20used%20for%20the%20job%20number%20consists%20of%20RE%20001%2C%20RE%20002%2C%20RE%20003%20all%20the%20way%20to%20RE%201486.%26nbsp%3B%20Within%20this%20numbering%20system%20there%20are%20numerous%20jobs%20with%20multiple%20rows%20under%20one%20RE%20number%20such%20as%20RE%20101%20dot%2001%2C%20RE%20101%20dot%2002%20etc.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESomething%20has%20happened%20over%20the%20weekend%20and%20the%20rows%20have%20been%20rearranged.%26nbsp%3B%20The%20rows%20go%20from%20RE%20001%20to%20RE%20100%2C%20then%20the%20next%20row%20number%20is%20RE%201000%20to%20RE%201009%2C%20then%20the%20next%20number%20is%20RE%20101%2C%20followed%20by%20RE%201010%2C%20RE%201011%2C%20RE%201012.%26nbsp%3B%20We%20have%20tried%20numerous%20ways%20of%20using%20the%20sort%20function%20on%20excel%20but%20we%20are%20unable%20to%20get%20the%20spreadsheet%20back%20to%20the%20way%20it%20was.%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2879813%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2879973%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sort%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1194732%22%20target%3D%22_blank%22%3E%40Gizwol79%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20share%20the%20workbook%20with%20the%20numbers%20i'm%20sure%20the%20numbers%20are%20not%20confidential%20just%20replace%20the%20job%20names%20with%20job01%20job02%20..etc%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

We have a job register that now consists of over 3000 rows.  It is a critical part of our business.  The first column consists of the job number and all other columns are relevant to the specific job. 

The numbering system we have used for the job number consists of RE 001, RE 002, RE 003 all the way to RE 1486.  Within this numbering system there are numerous jobs with multiple rows under one RE number such as RE 101 dot 01, RE 101 dot 02 etc.  

Something has happened over the weekend and the rows have been rearranged.  The rows go from RE 001 to RE 100, then the next row number is RE 1000 to RE 1009, then the next number is RE 101, followed by RE 1010, RE 1011, RE 1012.  We have tried numerous ways of using the sort function on excel but we are unable to get the spreadsheet back to the way it was. 

2 Replies

@Gizwol79 

 

can you share the workbook with the numbers i'm sure the numbers are not confidential just replace the job names with job01 job02 ..etc

@Gizwol79 Combining the "RE " and a number creates a text. So, now you are sorting texts and "RE 101" gets sorted after "RE 1009" just like "AB" would be sorted after "A" but before "B". To avoid this problem, all the codes should have the exact same structure. For example "RE nnnn.nn", meaning RE followed by 4 numbers, then a dot and then 2 more numbers.

 

But for now, to sort the rows back into their original order, you need to split the RE-codes into three segments, so that you get 3 columns one with only "RE ", another with numbers 1 to 1486  and a third one that may contain numbers 1 to 99.

 

First save a copy of your current workbooks and work on that one. Insert three columns to the right of the column with the (now incorrectly sorted) RE-codes. Copy the original RE-codes to the first inserted column and select it.

 

On the Data ribbon, choose Text to Columns. In step 1, choose "Delimited". Next. In step 2 choose "Space". Next. In step 3, in the preview window, click on the second column and select the Text button. Finish.

 

Now, do something similar with the second column that contains the nnn.nn part. This time, select "Other" in step 2 and enter a dot as the delimiter and in step 3 leave the data types at "General". Finish.

 

Now you'll have the three columns. On with "RE ", one with real numbers 1 to 1486 and one with real numbers 1 to 99. Let's give these three columns a header. Call them Part1, Part2 and Part3. Something like this:

Screenshot 2021-10-25 at 07.43.47.png

Now select columns with Part2 and Part3. On the Home ribbon, Sort & Filter, Custom Sort. (Sort by) column Part2, Smallest to Largest. Add another sort level (Then by) column Part3, Smallest to Largest. OK. Now, the rows should be in their original places again.

 

If you want, you can now re-structure the project codes by combining the three columns like this:

Screenshot 2021-10-25 at 07.58.07.png