Difficult Sorting Problem
Hi,
I would like to thank all in advance for helping with this. Here is my problem: I am trying to make a spreadsheet to list all of our inventory items. We currently use Great Plains as our financial and manufacturing software, and all of our inventory numbers are like this: 1-10-3001 1-10-3099 1-11-3013 1-12-3005 2-15-2099 etc. (We have approx. 7,000 inventory items) Now here is the dilema, the first sets of numbers (2-15-XXXX) are for the computer use, and the the last four (X-XX-2099) are how the parts are sorted and used by the people on the floor. So I get a report from Great Plains that is sorted by the first digits, but I need the report sorted by the last four digits of that part number : 2-15-2099 1-10-3001 1-12-3005 1-11-3013 1-10-3099 I have experimented with macros and other ways, to no avail. Also as a note the first column is the part numbers, 2nd column is the part description, 3rd column is the unit of measure, and 4th column is the quantity on hand. Again, any help will be greatly appriciated. -- Thanks, Rob |
Try sorting on a helper col, say col F?
Put in F2: =RIGHT(A2,4)*1 Copy down Select the data range inclusive of col F Click Data Sort Sort by col F (Ascending) OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Rob wrote in message ... Hi, I would like to thank all in advance for helping with this. Here is my problem: I am trying to make a spreadsheet to list all of our inventory items. We currently use Great Plains as our financial and manufacturing software, and all of our inventory numbers are like this: 1-10-3001 1-10-3099 1-11-3013 1-12-3005 2-15-2099 etc. (We have approx. 7,000 inventory items) Now here is the dilema, the first sets of numbers (2-15-XXXX) are for the computer use, and the the last four (X-XX-2099) are how the parts are sorted and used by the people on the floor. So I get a report from Great Plains that is sorted by the first digits, but I need the report sorted by the last four digits of that part number : 2-15-2099 1-10-3001 1-12-3005 1-11-3013 1-10-3099 I have experimented with macros and other ways, to no avail. Also as a note the first column is the part numbers, 2nd column is the part description, 3rd column is the unit of measure, and 4th column is the quantity on hand. Again, any help will be greatly appriciated. -- Thanks, Rob |
Try this:
First, assuming your inventory numbers are in column A, in column B write: =right(A1,4) This will show only the 4 digits to the right. Then do a data sort using column B as the sorting column. This should solve your problem. Chris "Rob" wrote: Hi, I would like to thank all in advance for helping with this. Here is my problem: I am trying to make a spreadsheet to list all of our inventory items. We currently use Great Plains as our financial and manufacturing software, and all of our inventory numbers are like this: 1-10-3001 1-10-3099 1-11-3013 1-12-3005 2-15-2099 etc. (We have approx. 7,000 inventory items) Now here is the dilema, the first sets of numbers (2-15-XXXX) are for the computer use, and the the last four (X-XX-2099) are how the parts are sorted and used by the people on the floor. So I get a report from Great Plains that is sorted by the first digits, but I need the report sorted by the last four digits of that part number : 2-15-2099 1-10-3001 1-12-3005 1-11-3013 1-10-3099 I have experimented with macros and other ways, to no avail. Also as a note the first column is the part numbers, 2nd column is the part description, 3rd column is the unit of measure, and 4th column is the quantity on hand. Again, any help will be greatly appriciated. -- Thanks, Rob |
All times are GMT +1. The time now is 11:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com