Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mark1caroline
 
Posts: n/a
Default Stock Location Sorting Problem


Hi all,

I have been using Excel for a few years now, but I have only really
scratched its surface.

I have been using Excel to great effect to hold all my stock records,
item part number, prices, manufacturer, description and so on. I
recently been trying to add stock location data, this has worked well
but for one problem, I am approaching a stock count and have tried to
sort the data by bin location. I was expecting the data to be arranged
in an order of location but Excel is jumbling the locations up which
make a stock count difficult.

My location data is AlphaNumeric but this could be changed for a better
string of data if neccesary, but it needs to give clear location data,
which is logical to stores staff.

The locations I have set up consists of a Letter of Rack, Letter of
shelf on the rack, and number of location on the shelf. This would
result in a location of say, AA1, which would be rack A, shelf A, and
location 1. This works fine until AA10 is reached, it puts location 10
in the wrong postion on the spread sheet. I have tried breaking the
string up with dots and slashes. I have tried formatting the cells to
text, numbers and others.

Please help my stock check is looming, and with over 4000 parts to
check with some 500 located parts this will be a long job.

I hope I have described my situation so it can be understood, hopefully
an Excel guru out there may have an answer I am sure there is an easy
way around this problem.

Thanks in advance for any help.

Regards

Mark


--
mark1caroline
------------------------------------------------------------------------
mark1caroline's Profile: http://www.excelforum.com/member.php...o&userid=25165
View this thread: http://www.excelforum.com/showthread...hreadid=386629

  #2   Report Post  
Jim Cone
 
Posts: n/a
Default

Mark,

You can insert an additional column and construct
a formula to extract the numbers only from each cell.
Then sort on that column.
This is done for you in my brand new "Special Sort" Excel add-in.
I am calling it the beta release. It provides seven different
sort options not readily available in Excel.

Free upon direct request to the adventurous, no instructions are
written yet. - remove XXX from my email address.

Jim Cone
San Francisco, USA
XX



"mark1caroline"

wrote in message
news:mark1caroline.1s2x6j_1121209516.3919@excelfor um-nospam.com...
Hi all,
I have been using Excel for a few years now, but I have only really
scratched its surface.
I have been using Excel to great effect to hold all my stock records,
item part number, prices, manufacturer, description and so on. I
recently been trying to add stock location data, this has worked well
but for one problem, I am approaching a stock count and have tried to
sort the data by bin location. I was expecting the data to be arranged
in an order of location but Excel is jumbling the locations up which
make a stock count difficult.
My location data is AlphaNumeric but this could be changed for a better
string of data if neccesary, but it needs to give clear location data,
which is logical to stores staff.
The locations I have set up consists of a Letter of Rack, Letter of
shelf on the rack, and number of location on the shelf. This would
result in a location of say, AA1, which would be rack A, shelf A, and
location 1. This works fine until AA10 is reached, it puts location 10
in the wrong postion on the spread sheet. I have tried breaking the
string up with dots and slashes. I have tried formatting the cells to
text, numbers and others.
Please help my stock check is looming, and with over 4000 parts to
check with some 500 located parts this will be a long job.
I hope I have described my situation so it can be understood, hopefully
an Excel guru out there may have an answer I am sure there is an easy
way around this problem.
Thanks in advance for any help.
Regards
Mark
--
mark1caroline

  #3   Report Post  
mark1caroline
 
Posts: n/a
Default


Hi Jim, Thanks for reply.

Is there not a string of logical numbers and or letters that could be
entered into a sheet that would sort in order? There must be a simple
answer to this annoying problem I have It would really need to
contain both letters and numbers to make sence to stores staff. Excel
is such a powerful tool yet what appears to me to be a simple sort can
not be achieved.

Mark


--
mark1caroline
------------------------------------------------------------------------
mark1caroline's Profile: http://www.excelforum.com/member.php...o&userid=25165
View this thread: http://www.excelforum.com/showthread...hreadid=386629

  #4   Report Post  
Jim Cone
 
Posts: n/a
Default

Mark,

There is no built-in way to do that.
Setting up your numbering system so that all numeric entries
were the same length (1234, 0234, 0004) would be best -
if you had a time machine.<g

The Special Sort add-in I mentioned will sort the way you want.
Also, someone may yet offer a formula fix the numbers.
I've seen Leo Heuser and others work miracles.

Regards,
Jim Cone
San Francisco, USA


"mark1caroline"

wrote in message
news:mark1caroline.1s3m70_1121241945.1036@excelfor um-nospam.com...
Hi Jim, Thanks for reply.
Is there not a string of logical numbers and or letters that could be
entered into a sheet that would sort in order? There must be a simple
answer to this annoying problem I have It would really need to
contain both letters and numbers to make sence to stores staff. Excel
is such a powerful tool yet what appears to me to be a simple sort can
not be achieved.
Mark

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem with msn money stock quote add in [email protected] Excel Worksheet Functions 2 July 15th 05 01:00 AM
sorting problem in excel 2000 inenewbl Excel Discussion (Misc queries) 1 June 13th 05 04:48 AM
Problem with Sorting Data reggiebu Excel Discussion (Misc queries) 2 June 9th 05 09:37 PM
problem saving workbook to network location eddie Excel Discussion (Misc queries) 0 April 14th 05 09:28 PM
Sorting problem JC Excel Discussion (Misc queries) 3 January 28th 05 04:27 AM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"