I am using Excel 2007.

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
come first, 3 and 4 next, 5 and 6 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
02-1
02-2
03-1
03-2
01-3
01-4
02-3
02-4
03-3
03-4
01-5
01-6
02-5
02-6
03-5
03-6

Am I asking the impossible?
--
tb

On Thu, 6 Nov 2014 01:00:33 +0000 (UTC), "tb" wrote:

I am using Excel 2007.

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
come first, 3 and 4 next, 5 and 6 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
02-1
02-2
03-1
03-2
01-3
01-4
02-3
02-4
03-3
03-4
01-5
01-6
02-5
02-6
03-5
03-6

Am I asking the impossible?

Not at all. You don't even need VBA, but you do need to use a "helper" column (a column used only for the purposes of sorting, which you can delete or hide after you're done with it)

Assuming your data is in Column A:

B1: =ROUND(RIGHT(A1,1)/2,0)

and fill down as far as required.

Then SORT: first on Column B; then on Column A

On 11/7/2014 at 2:57:54 PM Ron Rosenfeld wrote:

On Thu, 6 Nov 2014 01:00:33 +0000 (UTC), "tb"
wrote:

I am using Excel 2007.

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 come first, 3 and 4 next, 5 and 6 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
02-1
02-2
03-1
03-2
01-3
01-4
02-3
02-4
03-3
03-4
01-5
01-6
02-5
02-6
03-5
03-6

Am I asking the impossible?

Not at all. You don't even need VBA, but you do need to use a
"helper" column (a column used only for the purposes of sorting,
which you can delete or hide after you're done with it)

Assuming your data is in Column A:

B1: =ROUND(RIGHT(A1,1)/2,0)

and fill down as far as required.

Then SORT: first on Column B; then on Column A

Works great! Thanks.

--
tb