Solved

Sort function in excel

Posted on 2016-09-28
6
38 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 33

Accepted Solution

by:
Rob Henson earned 250 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 250 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

829 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