ExcelBanter - Help With Sorting

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Help With Sorting (https://www.excelbanter.com/excel-worksheet-functions/451244-help-sorting.html)

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

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

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

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

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