Creating a Distance Table

Hi,

I'm interested in creating a distance table by the easiest possible way in
Excel 2003. With a list of places going down the sheet, can anyone suggest
how I get a formula to copy these cells into other cells going along the top?
I assume this could be adapted or directly used to duplicate distances as
neccassary. For example

Place1 Place2 Place3...
Place1 0 6 4
Place2 6 0 2
Place3.... 4 2 0

Any ideas appreciated.

Copy > PasteSpecial > Transpose

Vaya con Dios,
Chuck, CABGx3



"mkj" wrote:

> Hi,
>
> I'm interested in creating a distance table by the easiest possible way in
> Excel 2003. With a list of places going down the sheet, can anyone suggest
> how I get a formula to copy these cells into other cells going along the top?
> I assume this could be adapted or directly used to duplicate distances as
> neccassary. For example
>
> Place1 Place2 Place3...
> Place1 0 6 4
> Place2 6 0 2
> Place3.... 4 2 0
>
> Any ideas appreciated.

That's close, but it won't let me paste over the original selection to get
the end result I'm after. I was also wondering if a formula would do it so I
can insert new places as I go, up to a total of 165 places. I'm not planning
on typing that all in, in one go...


Thanks for your quick response before!


"CLR" wrote:

> Copy > PasteSpecial > Transpose
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "mkj" wrote:
>
> > Hi,
> >
> > I'm interested in creating a distance table by the easiest possible way in
> > Excel 2003. With a list of places going down the sheet, can anyone suggest
> > how I get a formula to copy these cells into other cells going along the top?
> > I assume this could be adapted or directly used to duplicate distances as
> > neccassary. For example
> >
> > Place1 Place2 Place3...
> > Place1 0 6 4
> > Place2 6 0 2
> > Place3.... 4 2 0
> >
> > Any ideas appreciated.

If the cities start in A2 and go down, put this in B1:

=INDEX($A$2:$A$20,COLUMN(A1))

And copy to the right. Of course $A$20 is variable.

--
Kind regards,

Niek Otten

"mkj" <mkj@discussions.microsoft.com> wrote in message
news:A92D8A14-B850-4FD2-968E-610824B840F2@microsoft.com...
> Hi,
>
> I'm interested in creating a distance table by the easiest possible way in
> Excel 2003. With a list of places going down the sheet, can anyone suggest
> how I get a formula to copy these cells into other cells going along the
> top?
> I assume this could be adapted or directly used to duplicate distances as
> neccassary. For example
>
> Place1 Place2 Place3...
> Place1 0 6 4
> Place2 6 0 2
> Place3.... 4 2 0
>
> Any ideas appreciated.

That's pretty cool. It certainly copies the place names easily enough.
I guess that for each row I would have to individually change it to
something like
(in column E)
=INDEX($B$2:$B$20,COLUMN(E2)) in row 2
=INDEX($C$2:$C$20,COLUMN(E3)) in row 3 etc etc to get the distances copied
from one side to the other. If I do it manually, that is still 165 changes to
make. Can you think of an easier way of changing the parameters, or would I
be looking at a macro? *Shudder*

Thanks very much,

MKJ

"Niek Otten" wrote:

> If the cities start in A2 and go down, put this in B1:
>
> =INDEX($A$2:$A$20,COLUMN(A1))
>
> And copy to the right. Of course $A$20 is variable.
>
> --
> Kind regards,
>
> Niek Otten
>
> "mkj" <mkj@discussions.microsoft.com> wrote in message
> news:A92D8A14-B850-4FD2-968E-610824B840F2@microsoft.com...
> > Hi,
> >
> > I'm interested in creating a distance table by the easiest possible way in
> > Excel 2003. With a list of places going down the sheet, can anyone suggest
> > how I get a formula to copy these cells into other cells going along the
> > top?
> > I assume this could be adapted or directly used to duplicate distances as
> > neccassary. For example
> >
> > Place1 Place2 Place3...
> > Place1 0 6 4
> > Place2 6 0 2
> > Place3.... 4 2 0
> >
> > Any ideas appreciated.
>
>
>

=INDEX($A$1:$K$11,MATCH(K$1,$A$1:$A$11,0),MATCH($A2,$A$1:$K$1))

This formula would be put in column K (as an example) and copied to all cells
above the diagonal 0 line. Then all you would have to do is change the
distances on the bottom half and the top half would be updated automatically.

To clarify what this is doing:

$A$1:$K$11 is the entire range of data
K$1 the K is whatever column you are putting the formula in and 1 is the row
where your cities are listed across the top.

all other values would have to be adjusted accordingly to reflect your data.

Is this what you are looking for? It is hard to explain and I feel like I may have
done a poor job. If you are familiar with excel functions then you can probably
play around with it and get it to work.

Example only....adjust ranges and cities.

You have a table A1:J10

Leave A1 blank.

Say A2:A10 contains city and B1:J1 contains city1

e.g

A2 = Quebec
B1 = Quebec1

A3 = Ottawa
C1 = Ottawa1

A4 = Timmins
D1 = Timmins1

B2:J10 contains mileages.

One method is to select the entire table A1:J10 then choose
Insert>Name>Create, and select top row and left column.

Then use the intersect functionality:

=city city1

In above example =quebec ottawa1


This will return the value of the cell at the intersection of city and city1


Gord Dibben Excel MVP




On Mon, 6 Mar 2006 06:48:34 -0800, mkj <mkj@discussions.microsoft.com> wrote:

>Hi,
>
>I'm interested in creating a distance table by the easiest possible way in
>Excel 2003. With a list of places going down the sheet, can anyone suggest
>how I get a formula to copy these cells into other cells going along the top?
>I assume this could be adapted or directly used to duplicate distances as
>neccassary. For example
>
> Place1 Place2 Place3...
>Place1 0 6 4
>Place2 6 0 2
>Place3.... 4 2 0
>
>Any ideas appreciated.

Gord Dibben MS Excel MVP

Hi,

That's excellent! One formula that can be pasted anywhere in the upper half
and be expected to work. (Obviously modifying for different sized tables.)
Exactly what I was hoping to find. Thank-you very much for your help.

MKJ

"mphell0" wrote:

>
> =INDEX($A$1:$K$11,MATCH(K$1,$A$1:$A$11,0),MATCH($A2,$A$1:$K$1))
>
> This formula would be put in column K (as an example) and copied to all
> cells
> above the diagonal 0 line. Then all you would have to do is change the
>
> distances on the bottom half and the top half would be updated
> automatically.
>
> To clarify what this is doing:
>
> $A$1:$K$11 is the entire range of data
> K$1 the K is whatever column you are putting the formula in and 1 is
> the row
> where your cities are listed across the top.
>
> all other values would have to be adjusted accordingly to reflect your
> data.
>
> Is this what you are looking for? It is hard to explain and I feel
> like I may have
> done a poor job. If you are familiar with excel functions then you can
> probably
> play around with it and get it to work.
>
>
> --
> mphell0
> ------------------------------------------------------------------------
> mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
> View this thread: http://www.excelforum.com/showthread...hreadid=519326
>
>

Hello.

I'm afraid you lost me slightly there, though I did play with the feature to
Insert>Name... That could be useful later on.
mphello found a solution that I'll use, but thanks for your efforts on this
anyway! It's much appreciated.

MKJ

"Gord Dibben" wrote:

> Example only....adjust ranges and cities.
>
> You have a table A1:J10
>
> Leave A1 blank.
>
> Say A2:A10 contains city and B1:J1 contains city1
>
> e.g
>
> A2 = Quebec
> B1 = Quebec1
>
> A3 = Ottawa
> C1 = Ottawa1
>
> A4 = Timmins
> D1 = Timmins1
>
> B2:J10 contains mileages.
>
> One method is to select the entire table A1:J10 then choose
> Insert>Name>Create, and select top row and left column.
>
> Then use the intersect functionality:
>
> =city city1
>
> In above example =quebec ottawa1
>
>
> This will return the value of the cell at the intersection of city and city1
>
>
> Gord Dibben Excel MVP
>
>
>
>
> On Mon, 6 Mar 2006 06:48:34 -0800, mkj <mkj@discussions.microsoft.com> wrote:
>
> >Hi,
> >
> >I'm interested in creating a distance table by the easiest possible way in
> >Excel 2003. With a list of places going down the sheet, can anyone suggest
> >how I get a formula to copy these cells into other cells going along the top?
> >I assume this could be adapted or directly used to duplicate distances as
> >neccassary. For example
> >
> > Place1 Place2 Place3...
> >Place1 0 6 4
> >Place2 6 0 2
> >Place3.... 4 2 0
> >
> >Any ideas appreciated.
>
> Gord Dibben MS Excel MVP
>