• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Complex Sorting

J

Justin Greenwood

Member
Hi All

So I have a load of pick holes and they are labeled by:
ZONE (A,B,C ect)
LANE (01,02,03 ect)
LEVEL (A,B,C ect)
BIN (01,02,03 ect)

at the moment I can only sort it so it is like:
H01A01
H01A02
H01A03
H01A04
H01A05
H01A06
H01A07
H01A08
H01A09
H01A10

This means I have to pick all of A first then back to B and as each level has 99 bins this can take a long time what I need to try and do is sort it so it looks like this:


H01A01
H01B01
H01C01
H01D01
H01E01
H02A01
H02B01
H02C01
H02D01
H02E01
H01A02
H01B02
H01C02
H01D02
H01E02

I have attached just the H section in the example.

Thanks for you help guys!
 

Attachments

  • Loc.xlsx
    114 KB · Views: 11
if you sort by Column E, you will get below result
H01A01
H01B01
H01C01
H01D01
H01E01
H02A01
H02B01
H02C01
H02D01
H02E01
H03A01
H03B01
H03C01
H03D01
H03E01

its listing 01s first.
 
I wish it was that easy as I need it to go like:
H01A01
H01B01
H01C01
H01D01
H01E01
H02A01
H02B01
H02C01
H02D01
H02E01
H01A02
H01B02
H01C02
H01D02
H01E02

If you look at it like:
pick the left side H01 starting a level A to E in location 01
pick the right side H02 starting a level A to E in location 01
pick the left side H01 starting a level A to E in location 02
pick the right side H02 starting a level A to E in location 02

H 01 A B C D E 02 ^ 02 E D C B A 02 H
H 01 A B C D E 01 ^ 01 E D C B A 02 H
 
Justin Greenwood said:
I wish it was that easy as I need it to go like:
H01A01
H01B01
H01C01
H01D01
H01E01
H02A01
H02B01
H02C01
H02D01
H02E01
H01A02
H01B02
H01C02
H01D02
H01E02

If you look at it like:
pick the left side H01 starting a level A to E in location 01
pick the right side H02 starting a level A to E in location 01
pick the left side H01 starting a level A to E in location 02
pick the right side H02 starting a level A to E in location 02

H 01 A B C D E 02 ^ 02 E D C B A 02 H
H 01 A B C D E 01 ^ 01 E D C B A 02 H
Click to expand...
Hi,

Like this?
Please see attached
 

Attachments

  • Loc.xlsx
    151 KB · Views: 7
Close but it is still going:
H01A01
H01B01
H01C01
H01D01
H01E01
H02A01
H02B01
H02C01
H02D01
H02E01
H03A01 this should be H01A02
H03B01 this should be H01B02
H03C01 this should be H01C02
H03D01 this should be H01D02
H03E01 this should be H01E02

I think I might have to do it an aisle at a time :(
 
Justin Greenwood said:
Close but it is still going:
H01A01
H01B01
H01C01
H01D01
H01E01
H02A01
H02B01
H02C01
H02D01
H02E01
H03A01 this should be H01A02
H03B01 this should be H01B02
H03C01 this should be H01C02
H03D01 this should be H01D02
H03E01 this should be H01E02

I think I might have to do it an aisle at a time :(
Click to expand...
Ok, so when would you start with H03A01?
Is it 2 at a time like 01-01 then 02-01 then 01-02 then 02-02 then 03-01...?
 
H01 would work with H02
H03 would work with H04 and so on.
In this area they go up A-E and then along to 01-78

So H03A01 would come after H02E78
 
I have attached a sample of the layout for you green = pic holes the grey is the walk way.
I want it so they can pick left and right in one pass
 

Attachments

  • LAYOUT.xlsx
    15.5 KB · Views: 4
Got it!!! Changed:
Code: 
=
IF(OR(MID([@LOC],2,2)="01",MID([@LOC],2,2)="02"),1,
IF(OR(MID([@LOC],2,2)="03",MID([@LOC],2,2)="04"),2,
IF(OR(MID([@LOC],2,2)="05",MID([@LOC],2,2)="06"),3,
IF(OR(MID([@LOC],2,2)="07",MID([@LOC],2,2)="08"),4,
IF(OR(MID([@LOC],2,2)="09",MID([@LOC],2,2)="10"),5,
IF(OR(MID([@LOC],2,2)="11",MID([@LOC],2,2)="12"),6,
IF(OR(MID([@LOC],2,2)="13",MID([@LOC],2,2)="14"),7,
IF(OR(MID([@LOC],2,2)="15",MID([@LOC],2,2)="16"),8,
IF(OR(MID([@LOC],2,2)="17",MID([@LOC],2,2)="18"),9,
IF(OR(MID([@LOC],2,2)="19",MID([@LOC],2,2)="20"),10,
11))))))))))&RIGHT([@LOC],2)&MID([@LOC],2,2)&(MID([@LOC],4,1)

TO

Code: 
=
IF(OR(MID([@LOC],2,2)="01",MID([@LOC],2,2)="02"),1,
IF(OR(MID([@LOC],2,2)="03",MID([@LOC],2,2)="04"),2,
IF(OR(MID([@LOC],2,2)="05",MID([@LOC],2,2)="06"),3,
IF(OR(MID([@LOC],2,2)="07",MID([@LOC],2,2)="08"),4,
IF(OR(MID([@LOC],2,2)="09",MID([@LOC],2,2)="10"),5,
IF(OR(MID([@LOC],2,2)="11",MID([@LOC],2,2)="12"),6,
IF(OR(MID([@LOC],2,2)="13",MID([@LOC],2,2)="14"),7,
IF(OR(MID([@LOC],2,2)="15",MID([@LOC],2,2)="16"),8,
IF(OR(MID([@LOC],2,2)="17",MID([@LOC],2,2)="18"),9,
IF(OR(MID([@LOC],2,2)="19",MID([@LOC],2,2)="20"),10,
11))))))))))&RIGHT([@LOC],2)&MID([@LOC],2,2)&
IF(MID([@LOC],4,1)="A",1,
IF(MID([@LOC],4,1)="B",2,
IF(MID([@LOC],4,1)="C",3,
IF(MID([@LOC],4,1)="D",4,
IF(MID([@LOC],4,1)="E",5,
IF(MID([@LOC],4,1)="F",6,
IF(MID([@LOC],4,1)="G",7,
IF(MID([@LOC],4,1)="H",8,9))))))))
 
You must log in or register to reply here.
Back
Top