Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2021, 10:21 PM
Marcia's Avatar
Marcia Marcia is offline Sort alphanumeric data using SORTBY Windows 7 32bit Sort alphanumeric data using SORTBY Office 2013
Expert
Sort alphanumeric data using SORTBY
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Sort alphanumeric data using SORTBY

Hi. The SORTBY formula sorted the data in a column as follows:

GLUE, all purpose, 130 grams


GLUE, all purpose, 200 grams
GLUE, all purpose, 40 grams

I would like the sort result to be:

GLUE, all purpose, 40 grams
GLUE, all purpose, 130 grams
GLUE, all purpose, 200 grams

The usual help would be much appreciated. Thank you.
Attached Files
File Type: xlsx SortByAlphanumeric.xlsx (15.3 KB, 10 views)
Reply With Quote
  #2  
Old 04-13-2021, 08:34 AM
p45cal's Avatar
p45cal p45cal is offline Sort alphanumeric data using SORTBY Windows 10 Sort alphanumeric data using SORTBY Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

You're going to struggle to do this, because the values in the Item column are treated as text. So the sorting looks at characters one at a time from the left until they're different, so 1 comes before 6 even if there are zeroes after the 1.
You could try to extract the numerical value into a different column but it won't be easy.
You could also try to edit the values so that there are leading zeroes, so instead of:
RING BINDER, plastic, 6mm x 1.2m
you have
RING BINDER, plastic, 06mm x 1.2m


and instead of:
STAPLE WIRE, heavy duty, binder type, 23/6
you edit it to:
STAPLE WIRE, heavy duty, binder type, 23/06


It'll get difficult when you have the likes of:
1 1/2"
and

1 3/8"
because the 3/8ths is smaller than the 1/2 numerically, but not alphabetically.
Reply With Quote
  #3  
Old 04-13-2021, 04:08 PM
Marcia's Avatar
Marcia Marcia is offline Sort alphanumeric data using SORTBY Windows 7 32bit Sort alphanumeric data using SORTBY Office 2013
Expert
Sort alphanumeric data using SORTBY
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Your are right, it is a struggle. I tried adding helper columns for size and measurement (mm, ml, g..) but still 23/13 came before 23/6 or 23/06 so the helper columns were discarded. I guess we will have to live with the not so perfect sort results.
Reply With Quote
  #4  
Old 04-13-2021, 04:45 PM
p45cal's Avatar
p45cal p45cal is offline Sort alphanumeric data using SORTBY Windows 10 Sort alphanumeric data using SORTBY Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
but still 23/13 came before 23/06
If I edited to 23/06 on the List sheet it came out in the correct order on ListSort sheet!
Reply With Quote
  #5  
Old 04-14-2021, 10:10 AM
Marcia's Avatar
Marcia Marcia is offline Sort alphanumeric data using SORTBY Windows 7 32bit Sort alphanumeric data using SORTBY Office 2013
Expert
Sort alphanumeric data using SORTBY
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Ooops. My mistake. Yes, placing a zero before the single digits would sort the data the way they should be sorted. Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort alphanumeric data using SORTBY Sort data by month and by dates Marcia Excel 4 05-30-2018 01:51 AM
Sort alphanumeric data using SORTBY Please Help. I need to sort Data A-Z not sure if i need to use Macros steveyjohn Excel 1 11-14-2014 06:19 PM
Sort alphanumeric data using SORTBY When I sort my data, the sumif report changes amaka Excel 6 03-19-2014 04:55 AM
sort data in excel cabot Excel 1 08-17-2012 09:41 AM
Sort alphanumeric data using SORTBY Sort Data rkeles Excel 6 02-04-2012 01:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft