I have a spreadsheet that I am populating with miles from point to point.
Each spreadsheet has the same point tp oint listed twice, the second time in
reverse order. I would love to have a formula that looks throught the entire
K Column for the point in the D column and the D column for the point in the
K column and return the value from the same row in the P column.
Basically once I have looked up the miles from a store to another store I
don't want to have to find the row below that has the same two store in
reverse order and populate the miles field with the same information
Is this possible?

Have you thought about doing the mileage chart as a 2-D table? Four
stores A to D would be laid out like this:

A B C D
A -
B -
C -
D -

where the vertical list is the "from" store and the horizontal list is
the "to" store. Obviously, the diagonal is meaningless (or all zero).
The top triangle is a mirror image of the bottom triangle, although
you could just have a single triangle, as it doesn't really matter
which is "from" and which is "to". You often get such mileage charts
in road atlases.

Hope this helps.

Pete

On Aug 29, 11:49*pm, Paul W <Paul
wrote:
I have a spreadsheet that I am populating with miles from point to point.
Each spreadsheet has the same point tp oint listed twice, the second time in
reverse order. I would love to have a formula that looks throught the entire
K Column for the point in the D column and the D column for the point in the
K column and return the value from the same row in the P column.
Basically once I have looked up the miles from a store to another store I
don't want to have to find the row below that has the same two store in
reverse order and populate the miles field with the same information
Is this possible?

Pete_UK wrote:
Have you thought about doing the mileage chart as a 2-D table? Four
stores A to D would be laid out like this:

A B C D
A -
B -
C -
D -

where the vertical list is the "from" store and the horizontal list is
the "to" store. Obviously, the diagonal is meaningless (or all zero).
The top triangle is a mirror image of the bottom triangle, although
you could just have a single triangle, as it doesn't really matter
which is "from" and which is "to". You often get such mileage charts
in road atlases.

Hope this helps.

Pete

On Aug 29, 11:49 pm, Paul W <Paul
wrote:
I have a spreadsheet that I am populating with miles from point to point.
Each spreadsheet has the same point tp oint listed twice, the second time in
reverse order. I would love to have a formula that looks throught the entire
K Column for the point in the D column and the D column for the point in the
K column and return the value from the same row in the P column.
Basically once I have looked up the miles from a store to another store I
don't want to have to find the row below that has the same two store in
reverse order and populate the miles field with the same information
Is this possible?


Pete,

Good idea!

Pete, Thanks for the idea. It is a good one. The problem is, I have to have a
programmer import the file into an AS400 program once I have figured the time
and miles and the 2d won't work for that.
Does anyone using maybe VBA have any ideas?

"smartin" wrote:

Pete_UK wrote:
Have you thought about doing the mileage chart as a 2-D table? Four
stores A to D would be laid out like this:

A B C D
A -
B -
C -
D -

where the vertical list is the "from" store and the horizontal list is
the "to" store. Obviously, the diagonal is meaningless (or all zero).
The top triangle is a mirror image of the bottom triangle, although
you could just have a single triangle, as it doesn't really matter
which is "from" and which is "to". You often get such mileage charts
in road atlases.

Hope this helps.

Pete

On Aug 29, 11:49 pm, Paul W <Paul
wrote:
I have a spreadsheet that I am populating with miles from point to point.
Each spreadsheet has the same point tp oint listed twice, the second time in
reverse order. I would love to have a formula that looks throught the entire
K Column for the point in the D column and the D column for the point in the
K column and return the value from the same row in the P column.
Basically once I have looked up the miles from a store to another store I
don't want to have to find the row below that has the same two store in
reverse order and populate the miles field with the same information
Is this possible?


Pete,

Good idea!

Can you post a small sample of the data that demonstrates what you're
looking for? From your written description I'm have a hard time visualizing
the setup and what you're trying to do.

--
Biff
Microsoft Excel MVP


"Paul W" wrote in message
...
Pete, Thanks for the idea. It is a good one. The problem is, I have to
have a
programmer import the file into an AS400 program once I have figured the
time
and miles and the 2d won't work for that.
Does anyone using maybe VBA have any ideas?

"smartin" wrote:

Pete_UK wrote:
Have you thought about doing the mileage chart as a 2-D table? Four
stores A to D would be laid out like this:

A B C D
A -
B -
C -
D -

where the vertical list is the "from" store and the horizontal list is
the "to" store. Obviously, the diagonal is meaningless (or all zero).
The top triangle is a mirror image of the bottom triangle, although
you could just have a single triangle, as it doesn't really matter
which is "from" and which is "to". You often get such mileage charts
in road atlases.

Hope this helps.

Pete

On Aug 29, 11:49 pm, Paul W <Paul
wrote:
I have a spreadsheet that I am populating with miles from point to
point.
Each spreadsheet has the same point tp oint listed twice, the second
time in
reverse order. I would love to have a formula that looks throught the
entire
K Column for the point in the D column and the D column for the point
in the
K column and return the value from the same row in the P column.
Basically once I have looked up the miles from a store to another
store I
don't want to have to find the row below that has the same two store
in
reverse order and populate the miles field with the same information
Is this possible?


Pete,

Good idea!

I will try, The spreadsheet is at work and i am home now.
store address 2nd store address Miles
#1 1 1st st or #2 23 2nd st CA 485
#1 1 1st st or #3 48 a st ca 385
#1 1 1st st or #4 65 r st ca 426
#2 23 2nd st ca #1 1 1st st or This is
the info I need
#2 23 2nd st ca #3 48 a st ca This is
the info I need

"T. Valko" wrote:

Can you post a small sample of the data that demonstrates what you're
looking for? From your written description I'm have a hard time visualizing
the setup and what you're trying to do.

--
Biff
Microsoft Excel MVP


"Paul W" wrote in message
...
Pete, Thanks for the idea. It is a good one. The problem is, I have to
have a
programmer import the file into an AS400 program once I have figured the
time
and miles and the 2d won't work for that.
Does anyone using maybe VBA have any ideas?

"smartin" wrote:

Pete_UK wrote:
Have you thought about doing the mileage chart as a 2-D table? Four
stores A to D would be laid out like this:

A B C D
A -
B -
C -
D -

where the vertical list is the "from" store and the horizontal list is
the "to" store. Obviously, the diagonal is meaningless (or all zero).
The top triangle is a mirror image of the bottom triangle, although
you could just have a single triangle, as it doesn't really matter
which is "from" and which is "to". You often get such mileage charts
in road atlases.

Hope this helps.

Pete

On Aug 29, 11:49 pm, Paul W <Paul
wrote:
I have a spreadsheet that I am populating with miles from point to
point.
Each spreadsheet has the same point tp oint listed twice, the second
time in
reverse order. I would love to have a formula that looks throught the
entire
K Column for the point in the D column and the D column for the point
in the
K column and return the value from the same row in the P column.
Basically once I have looked up the miles from a store to another
store I
don't want to have to find the row below that has the same two store
in
reverse order and populate the miles field with the same information
Is this possible?


Pete,

Good idea!