Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sort function in excel

Posted on 2016-09-28
6
Medium Priority
?
74 Views
Last Modified: 2016-11-06
Hey, I have a inventory of all hardware and want to sort by PC name. ARL01, ARL02 etc but when my naming convention moves to the hundreds it doesn't sort properly.

Any advice?
SORT.PNG
0
Comment
Question by:Toni Swinfield
6 Comments
 
LVL 34

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 41819655
It is doing the sort alphabetically rather than numerically, thus all the items starting with 1 come before those starting with 2, hence 103 comes before 13.

I would suggest having a separate column that takes the number element of the inventory items and then sort on that column.

Alternatively, redefine your naming convention so that it includes 3 digits rather than only 2.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 1000 total points
ID: 41819748
As Rob suggested:
...I would suggest having a separate column that takes the number element of the inventory items and then sort on that column...

With your data in column [A], starting at row #1, enter this Array Formula* in cell [B1] (or any other column on the same row):

=IFERROR(VALUE(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$255),1)),0),255)),A1)

* To enter an Array Formula, use [CTRL]+[SHIFT]+[ENTER] at the end of the formula, rather than just [ENTER]/[Return]

Viewing the entered Array Formula in the cell will show it enclosed in { } curly braces:

{=IFERROR(VALUE(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$255),1)),0),255)),A1)}


Now copy this Array Formula from cell [B1] down column [ B ] until the extent of the data in column [A].

The end result will offer an additional column ([ B ]) with just the digits found towards the end of the corresponding value in column [A] but stored as a numerical value, rather than a string of text characters.

If a value in column [A] does not contain any digits the original value from column [A] will be shown instead.


You may now sort the data on this new column.

=IFERROR(VALUE(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$255),1)),0),255)),A1)
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 41821510
If the only problem is your ARLnn entries, where nn = 2 digits, you can use this formula to change them all into the format
Arl0nn
 - eg adding a central 0 to the entry

=IF(LEN(A1)=5,LEFT(A1,3)&"0"&RIGHT(A1,2),A1)

This formula tests for a 5 character code - ie one that is too short, and then creates a new entry, consisting of
the left 3 characters (Arl)  +  a central 0  +  the right 2 characters (01)

Note, this will work with ANY 5 digit code.  This may be good, or it may be bad... but you could refine it with a more complex IF statement.

If this works for you, you can then Copy the resulting column, and then Paste Special.. Values back ON TOP OF the original data.
Then, sort on that basis?
M--Personal-ee---add-central-zero.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Toni Swinfield
ID: 41832203
Thanks Rob Henson, due to lack of time, I've just added an extra figure in the naming convention in the column to sort from that.

All the formulas just fried my brain :/
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 41832509
Perhaps if you had replied on 28 September 2016, rather than over one week later (on 6 October 2016), we would have had more time to explain the formulae to you.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41875975
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Rob Henson (https:#a41819655)
-- [ fanpages ] (https:#a41819748)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question