• 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.

Non ascending order lookup - Electrical Pipe Scheduling

C

Cearr

New Member
HI Guys.


I am an Mechanical engineer doing cabling schedules around a site,

All these cables go though draw pits (DP) or man holes.

I can show what cables are in each draw pit.

What I have to show is what draw pit the cable has come form and where it is going to.

My problems is that these routes are not going to be in ascending order, therefore using 'LOOKUP' wont work


An example is probable the best way of showing what Im trying to achieve. On the first sheet I have all of my cables listed out, I then write out their routes through the plant, Starting with 1 for the starting draw pit.


SHEET 1


A B C D E F G

1 Cable DP1 DP2 DP3 DP4 DP5 DP6

2 P23 1 2 3 4 5 6


3 G10 1 5 2 3 4


4 P24 1 2 3 5 4


5 C29 1 2 3 4


6 P25 1 4 3 2


7 CR11 1 2 3


8 C31 1 3 2


I then have a page for each draw pit, so each draw pit should show:


DP1 SHEDULE

CABLE FORM TO

P23 DP2

G10 DP4

P24 DP2

C29 DP3

P25 DP4


DP2 SHEDULE

CABLE FORM TO

P23 DP1 DP3

G10 DP6

P24 DP1 DP3

P25 DP3

C31 DP5


DP3 SHEDULE

CABLE FORM TO

P23 DP2 DP4

P24 DP2 DP6

C29 DP1 DP5

P25 DP4 DP2

CR11 DP4


DP4 SHEDULE

CABLE FORM TO

P23 DP3 DP5

G10 DP1 DP5

P25 DP1 DP3

CR11 DP3 DP5

C31 DP5


DP5 SHEDULE

CABLE FORM TO

P23 DP4 DP6

G10 DP4 DP6

P24 DP6

C29 DP3 DP6

CR11 DP4

C31 DP2 DP4


DP6 SHEDULE

CABLE FORM TO

P23 DP5

G10 DP5 DP2

P24 DP3 DP5

C29 DP5


My thinking when I tried to do this was: I knew the number of each cable in each draw pit, (ie cable P23 in DP3 is going to be in cell D2 and is equal to 3) therefore to get where the cable has come form I take -1 (so for cable P23 in DP3 -1 get 2, which is DP2). For where the cable is going to I would add +1.


Here is where I get stuck, as they are not going to be in any ascending order and I have tried "Index & Match" but could not get it to work.


In my current Excel Sheet there is over 500 cable and 21 draw pits, and it is still at the design stage so cable routes keep changing routes. So Im trying to have everything on one page and not have to change the text in 7 or 8 pages.


I hope this is clear, (it probable isn't) if you have any questions please ask. Any help would be greatly appreciated, or if you other ways that I could try doing this.
 
In draw pits you have mentioned from and to (rare cases) data and this is what you are trying to achive?????
 
Cearr


Firstly welcome to the Chandoo.org Forums


a quick question?


Shouldn't


DP1 SHEDULE

CABLE FORM TO

P23 DP2

G10 DP4

P24 DP2

C29 DP3

P25 DP4


be


DP1 SHEDULE

CABLE FORM TO

P23 DP2

G10 DP3

P24 DP2

C29 DP3

P25 DP4
 
I am trying to have a sheet for each draw pit. On this sheet there is a list of cables.


I need to show on this sheet, what draw pit the cable has come from and where the draw pit is going.
 
hi Hui,


the route for G10 is DP1, DP4, DP5, DP6, DP2.


But I understand where your confusion is, How would one insert tabs or more spaces into a post??


The example as I read it now is more confusing than it should be...
 
Can you re-post the table with - in place of DP's which aren't used

as G10 clearly says G10 1 5 2 3 4, which says DP1-DP3-DP4-DP5-DP2

eg: G10 1 5 - 2 3 4
 
SHEET 1


A B C D E F G

1 Cable DP1 DP2 DP3 DP4 DP5 DP6

2 P23 1 2 3 4 5 6


3 G10 1 5 - 2 3 4


4 P24 1 2 3 - 5 4


5 C29 1 - - 2 3 4


6 P25 1 4 3 2 - -


7 CR11 - - 1 2 3 -


8 C31 - 1 - 3 2 -


I then have a page for each draw pit, so each draw pit should show:


DP1 SHEDULE

CABLE FORM TO

P23 - DP2

G10 - DP4

P24 - DP2

C29 - DP3

P25 - DP4


DP2 SHEDULE

CABLE FORM TO

P23 DP1 DP3

G10 DP6 -

P24 DP1 DP3

P25 DP3 -

C31 - DP5


DP3 SHEDULE

CABLE FORM TO

P23 DP2 DP4

P24 DP2 DP6

C29 DP1 DP5

P25 DP4 DP2

CR11 - DP4


DP4 SHEDULE

CABLE FORM TO

P23 DP3 DP5

G10 DP1 DP5

P25 DP1 DP3

CR11 DP3 DP5

C31 DP5 -


DP5 SHEDULE

CABLE FORM TO

P23 DP4 DP6

G10 DP4 DP6

P24 DP6 -

C29 DP3 DP6

CR11 DP4 -

C31 DP2 DP4


DP6 SHEDULE

CABLE FORM TO

P23 DP5 -

G10 DP5 DP2

P24 DP3 DP5

C29 DP5 -


d test
 
OK, I have worked it out.


As I am in my office, I dont have access to Shared website, so cant upload the excel file here.


Please share your email id here so that I can send you the file & you can upload the same here.


Formula what I have used is below entered in B13 against P23 & I know it will not work directly for you.


=IF(ISERROR(INDEX($A$1:$G$8,ROW()-ROW()+1,MATCH((INDEX($A$1:$G$8,MATCH(A13,$A$2:$A$8,0)+1,MATCH($A$11,$B$1:$G$1,0)+1)-1),A2:G2,0))=TRUE),"-",INDEX($A$1:$G$8,ROW()-ROW()+1,MATCH((INDEX($A$1:$G$8,MATCH(A13,$A$2:$A$8,0)+1,MATCH($A$11,$B$1:$G$1,0)+1)-1),A2:G2,0)))




*Where $A$11 contains the Drawpits number,(DP1, DP2, DP3 etc...)

*Also I have changed all "-" to blank in my table.


OR Else, wait for HUI to reply he will surely have some shorter formula/way to solve this.
 
TRYING TO EXPLAIN THE FORMULA


=INDEX("ENTIRE RANGE",1,MATCH(INDEX("ENTIRE RANGE",MATCH("P23","CABLE RANGE",0)+1,MATCH("DP1","Data Pipe Range",0)+1)-1),"RANGE OF P23",0)
 
My email is at bottom of page when you click on Excel Ninja below my avatar
 
Meedan has posted a solution to this problem and its a beauty

Well worth checking out at: https://rapidshare.com/files/1458604624/Data_Pipe_Line.xls
 
Thanks for all this guys, just back form site now, starting pulling cables tomorrow so might have to test at the weekend....
 
You must log in or register to reply here.
Back
Top