Solved

# Sort function in excel

Posted on 2016-09-28
Medium Priority
89 Views
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.

SORT.PNG
0
Question by:Toni Swinfield

LVL 35

Accepted Solution

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

[ 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.

0

LVL 23

Expert Comment

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?
0

Author Comment

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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.