Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SORTING DATA IN TWO COLUMNS (salary and name ) - sorting as perdescending order of salary [email protected] Excel Programming 0 July 26th 13 09:53 AM
Sorting Values Without Sorting Formulas SBX Excel Discussion (Misc queries) 2 April 12th 09 11:17 PM
Automatic sorting (giving max and min) based on custom sorting lis Joe Lewis[_2_] Excel Worksheet Functions 4 November 23rd 08 06:12 AM
Sorting VLookup vs Sorting SumProduct Lauren Excel Discussion (Misc queries) 1 August 21st 07 12:19 AM
Sorting ListBox results or transposing ListBox values to other cells for sorting Rob[_8_] Excel Programming 1 July 9th 03 04:35 AM


All times are GMT +1. The time now is 11:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"