Stock Location Sorting Problem

[mark1caroline]

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

 

[Jim Cone]

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
(e-mail address removed)



"mark1caroline"
<[email protected]>
wrote in message
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]

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

 

[Jim Cone]

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"
(e-mail address removed)>
wrote in message
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