Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Sorting
I am using Excel 2013 for Windows.
I am trying to sort some bin location in a specific way and don't know how to do it. We have a three-tiered vertical rack system in our warehouse that is divided in bin locations. For instance, rack No. 01 has bin locations 1 and 2 at the bottom, locations 3 and 4 in the middle tier, and locations 5 and 6 in the upper tier. Therefore a bin location could be something like 01-1, 01-2, 01-3, 01-4, 01-5, 01-6 where the first two digits are the rack number (01 in this case), followed by a dash ("-") and the third digit is the bin location in that rack (1, 2, 3, 4 etc. in this example). Locations 01-1 and 01-2 would be at the bottom of the vertical rack system, 01-3 and 01-4 would be in the middle, 01-5 and 01-6 would be at the top. I would like to sort by rack numbers but so that all locations 1 and 2, 3 and 4 are sorted together and locations 5 and 6 are sorted last. For instance, say that I have the following racks numbers and bin locations: 01-1 01-2 01-3 01-4 01-5 01-6 02-1 02-2 02-3 02-4 02-5 02-6 03-1 03-2 03-3 03-4 03-5 03-6 I would like to find a way to sort them this way: 01-1 01-2 01-3 01-4 02-1 02-2 02-3 02-4 03-1 03-2 03-3 03-4 01-5 01-6 02-5 02-6 03-5 03-6 Am I asking the impossible? -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Sorting
Perhaps you could relabel your racks to identify tiers...
01A-1 bin1, level1 01A-2 bin2, level1 01B-1 bin1, level2 01B-2 bin2, level2 01C-1 bin1, level3 01C-2 bin2, level3 Optionally, you possibly don't need the hyphen at all... 01A1 bin1, level1 01A2 bin2, level1 01B1 bin1, level2 01B2 bin2, level2 01C1 bin1, level3 01C2 bin2, level3 -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Sorting
On 12/31/2015 at 11:39:44 AM GS wrote:
Perhaps you could relabel your racks to identify tiers... 01A-1 bin1, level1 01A-2 bin2, level1 01B-1 bin1, level2 01B-2 bin2, level2 01C-1 bin1, level3 01C-2 bin2, level3 Optionally, you possibly don't need the hyphen at all... 01A1 bin1, level1 01A2 bin2, level1 01B1 bin1, level2 01B2 bin2, level2 01C1 bin1, level3 01C2 bin2, level3 Well, management does not want to do that. There are many, many racks and bins. it would take a while to re-label and change the information in our ERP system... -- tb |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Sorting
On 12/31/2015 at 11:39:44 AM GS wrote:
Perhaps you could relabel your racks to identify tiers... 01A-1 bin1, level1 01A-2 bin2, level1 01B-1 bin1, level2 01B-2 bin2, level2 01C-1 bin1, level3 01C-2 bin2, level3 Optionally, you possibly don't need the hyphen at all... 01A1 bin1, level1 01A2 bin2, level1 01B1 bin1, level2 01B2 bin2, level2 01C1 bin1, level3 01C2 bin2, level3 Well, management does not want to do that. There are many, many racks and bins. it would take a while to re-label and change the information in our ERP system... Poor management! Should have been better structured from the get-go, IMO!!! I didn't mean to 'physically' relabel your bins, just how you ref them in your system so they will sort how you wanted! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Sorting
Hi,
Am Thu, 31 Dec 2015 14:47:17 +0000 (UTC) schrieb tb: For instance, say that I have the following racks numbers and bin locations: 01-1 01-2 01-3 01-4 01-5 01-6 02-1 02-2 02-3 02-4 02-5 02-6 03-1 03-2 03-3 03-4 03-5 03-6 if your racks and bins are sorted as your example then try im B1: =IF(--RIGHT(A1,1)<5,ROW(),SUMPRODUCT(--(--RIGHT($A$1:$A$18,1)<5),--($A$1:$A$18<""))+ROW()) and copy down. Then sort by column B. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Sorting
Hi,
Am Thu, 31 Dec 2015 19:34:14 +0100 schrieb Claus Busch: if your racks and bins are sorted as your example then try im B1: =IF(--RIGHT(A1,1)<5,ROW(),SUMPRODUCT(--(--RIGHT($A$1:$A$18,1)<5),--($A$1:$A$18<""))+ROW()) and copy down. Then sort by column B. the formula is longer but more reliable: =IF(--RIGHT(A1,1)<5,LEFT(A1,FIND("-",A1)-1)*10+RIGHT(A1,1),LEFT(A1,FIND("-",A1)-1)*100*SUMPRODUCT(N(--RIGHT($A$1:$A$25,1)<5))+MID(A1,FIND("-",A1)+1,99)) Note that the range in the SUMPRODUCT section must be exactly the range with data. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SORTING DATA IN TWO COLUMNS (salary and name ) - sorting as perdescending order of salary | Excel Programming | |||
Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |