Solved

Sort function in excel

Posted on 2016-09-28
6
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

627 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