Solved

Sort function in excel

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

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:DanCh99
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 13

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now