Transportation Problem

Sager · May 21st, 2002, 12:48 PM

Here is a clarification on my origial posting. I made a couple of errors initially.

Column A represents the departure location
Column B represents the arrival location
Column C I want to represent the route.

A_____B_____C
Dep___Arr___Route
2007__2002
2003__2001
2001__2003
2005__2007
2005__2007
2003__2001
2001__2003
2007__2005

the route code array is below:
______________Destination
Origin
________2001____2002____2003____2005____2007
2001____________________2
2002____________________________________3
2003____2
2005____________________________________1
2007____________3_______________1

So going from 2007 to 2002 will be route 3. Similarily, going in reverse, from 2007 to 2002 will also be route 3.

Somehow, using the INDEX function, I should be able to enter a function to display either 1, 2 or 3 in Column C.

[ This Message was edited by: Sager on 2002-05-21 15:43 ]

Al Chara · May 21st, 2002, 03:18 PM

Can you define what the origin and destinations are for the acutal routes i.e. what routes 1, 2, and 3 are.

Mark W. · May 21st, 2002, 03:25 PM

How could Route 1 be "D" to "A" when "D" isn't listed as an origin?

Al Chara · May 21st, 2002, 06:04 PM

I have an answer for you but it is very dependent on how your sheet is setup.

I assumed the following data was A1:C9 where row 1 contains the headings Dep, Arr, Route.

Quote:

A_____B_____C
Dep___Arr___Route
2007__2002
2003__2001
2001__2003
2005__2007
2005__2007
2003__2001
2001__2003
2007__2005

Then I assumed the following matrix was in cells A11:F16 where A11 is blank.

Quote:

________2001____2002____2003____2005____2007
2001____________________2
2002____________________________________3
2003____2
2005____________________________________1
2007____________3_______________1

So, assuming the above setup, I put the following formula in cell C2 and copied it down to cell C9:

=OFFSET($A$11,MATCH(A2,$A$12:$A$16,0),MATCH(B2,$B$11:$F$11,0))

or

=INDEX($A$11:$F$16,MATCH(A2,$A$11:$A$16,0),MATCH(B2,$A$11:$F$11,0))

or using Yogi's suggestion with my ranges:

=VLOOKUP(A2,$A$11:$F$16,MATCH(B2,$A$11:$F$11),FALSE)

Tell me if there are any problems.

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-21 17:06 ]

[ This Message was edited by: Al Chara on 2002-05-21 17:44 ]

Yogi Anand · May 21st, 2002, 06:23 PM

Quote:

On 2002-05-21 11:48, Sager wrote:
Here is a clarification on my origial posting. I made a couple of errors initially.

Column A represents the departure location
Column B represents the arrival location
Column C I want to represent the route.

A_____B_____C
Dep___Arr___Route
2007__2002
2003__2001
2001__2003
2005__2007
2005__2007
2003__2001
2001__2003
2007__2005

the route code array is below:
______________Destination
Origin
________2001____2002____2003____2005____2007
2001____________________2
2002____________________________________3
2003____2
2005____________________________________1
2007____________3_______________1

So going from 2007 to 2002 will be route 3. Similarily, going in reverse, from 2007 to 2002 will also be route 3.

Somehow, using the INDEX function, I should be able to enter a function to display either 1, 2 or 3 in Column C.

[ This Message was edited by: Sager on 2002-05-21 15:43 ]

If your routes are set as a table in range A20:F25, and your start and destination are in cells A2 and B2, then the following formula should do

=VLOOKUP(A2,$A$20:$F$25,MATCH(B2,$A$20:$F$20,0),0)

Regards!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:02 ]

Yogi Anand · May 21st, 2002, 06:42 PM

Reproduced below is a simulation of the worksheet with the VLOOKUP solution:

Microsoft Excel - y020521.xls
A12		=
	A	B	C	D	E	F
1	Dep	Arr	Route
2	2007	2002	:alert('=VLOOKUP(A2,$A$20:$F$25,MATCH(B2,$A$20:$F$20,0),0)')>0
3	2003	2001	:alert('=VLOOKUP(A3,$A$20:$F$25,MATCH(B3,$A$20:$F$20,0),0)')>2
4	2001	2003	:alert('=VLOOKUP(A4,$A$20:$F$25,MATCH(B4,$A$20:$F$20,0),0)')>2
5	2005	2007	:alert('=VLOOKUP(A5,$A$20:$F$25,MATCH(B5,$A$20:$F$20,0),0)')>1
6	2005	2007	:alert('=VLOOKUP(A6,$A$20:$F$25,MATCH(B6,$A$20:$F$20,0),0)')>1
7	2003	2001	:alert('=VLOOKUP(A7,$A$20:$F$25,MATCH(B7,$A$20:$F$20,0),0)')>2
8	2001	2003	:alert('=VLOOKUP(A8,$A$20:$F$25,MATCH(B8,$A$20:$F$20,0),0)')>2
9	2007	2005	:alert('=VLOOKUP(A9,$A$20:$F$25,MATCH(B9,$A$20:$F$20,0),0)')>1
10
11
12
13
14
15		:alert('=MATCH(B2,A20:F20,0)')>3
16
17
18
19
20		2001	2002	2003	2005	2007
21	2001			2
22	2002					3
23	2003	2
24	2005					1
25	2007	3			1
Sheet3

Click on a hyperlinked cell to view the underlying formula

Al Chara · May 21st, 2002, 06:50 PM

Yogi, you have your route matrix setup wrong, but your formula will work if it is fixed.

[ This Message was edited by: Al Chara on 2002-05-21 17:51 ]

Yogi Anand · May 21st, 2002, 06:59 PM

Hi Al:
My route matrix is in cells A20:F25 -- Ya! I seeit now, it is in the last line -- it should have been route 3 from 2007 to 2002 and not 2001.

Thanks for keeping me honest!

Sager · May 22nd, 2002, 11:27 AM

Awesome!
Amazing responsiveness.
Thanks a million!
Till next time (and there will be a next time, assured),

Sager

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 12:48 PM	#1
Sager New Member Join Date: May 2002 Location: Vancouver, BC Posts: 2	Here is a clarification on my origial posting. I made a couple of errors initially. Column A represents the departure location Column B represents the arrival location Column C I want to represent the route. A_____B_____C Dep___Arr___Route 2007__2002 2003__2001 2001__2003 2005__2007 2005__2007 2003__2001 2001__2003 2007__2005 the route code array is below: ______________Destination Origin ________2001____2002____2003____2005____2007 2001____________________2 2002____________________________________3 2003____2 2005____________________________________1 2007____________3_______________1 So going from 2007 to 2002 will be route 3. Similarily, going in reverse, from 2007 to 2002 will also be route 3. Somehow, using the INDEX function, I should be able to enter a function to display either 1, 2 or 3 in Column C. [ This Message was edited by: Sager on 2002-05-21 15:43 ]

May 21st, 2002, 03:18 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Can you define what the origin and destinations are for the acutal routes i.e. what routes 1, 2, and 3 are.

May 21st, 2002, 03:25 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	How could Route 1 be "D" to "A" when "D" isn't listed as an origin?

May 21st, 2002, 06:50 PM	#7
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Yogi, you have your route matrix setup wrong, but your formula will work if it is fixed. [ This Message was edited by: Al Chara on 2002-05-21 17:51 ]

May 21st, 2002, 06:59 PM	#8
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Al: My route matrix is in cells A20:F25 -- Ya! I seeit now, it is in the last line -- it should have been route 3 from 2007 to 2002 and not 2001. Thanks for keeping me honest!

May 22nd, 2002, 11:27 AM	#9
Sager New Member Join Date: May 2002 Location: Vancouver, BC Posts: 2	Awesome! Amazing responsiveness. Thanks a million! Till next time (and there will be a next time, assured), Sager