matric values

[bird_brain9]

Hi
I have a matrix that was created in another program (looking at distance from one point to all other points) to be opened in Excel and the problem is that all the values I need are on the diagonal of the matrix. Does anybody know any quick way to get those values onto the same column from the diagonal so that I don't have to go through moving hundreds (thousands really) of individual values?
Thanks

 

[PaddyD]

hi - welcome to the board!

why don't you describe the data you've got in more detail & give us a worked example of what you're trying to do - we may be able to solve this efficiently without resorting to reorganising your data.

 

[tusharm]

I would think you want to keep the data in a matrix form (or at least a triangular matrix). That is the most effective way of looking up distances between different points (such as cities).

Hi
I have a matrix that was created in another program (looking at distance from one point to all other points) to be opened in Excel and the problem is that all the values I need are on the diagonal of the matrix. Does anybody know any quick way to get those values onto the same column from the diagonal so that I don't have to go through moving hundreds (thousands really) of individual values?
Thanks

 

[bird_brain9]

I used a GIS program to calculate the distance from one x,y coordinate location to every other x,y location within the file. The output is an Excel file with a "distance matrix." I need to run analyses on the distances between consecutive locations (the locations are separated by time), so I am looking to create a column that has the row values of the distance from location 1 to 2, 2 to 3, 3 to 4, and so on. The values I would like to place in one column are running on the diagonal of the matrix.

Book1
	A	B	C	D	E
1	location	1	2	3	4
2	1		34	23	92
3	2	34		47	56
4	3	23	47		75
5	4	92	56	75
Sheet1

Thanks for the help!

 

[PaddyD]

how about this sort of thing:

Book1
	A	B	C	D	E
1		1	2	3	4
2	1		34	23	92
3	2	24		47	56
4	3	23	47		75
5	4	92	56	75
6
7	From	2
8	To	4
9	Distance	56
Sheet3

formula is:

=INDEX($A$1:$E$5,MATCH(B7,$A$1:$A$5,0),MATCH(B8,$A$1:$E$1,0))

 

[bird_brain9]

Thanks, INDEX is what I needed. For some reason MATCH (as well as HLOOKUP and VLOOKUP) were not retreiving the values. However, with INDEX, I can just highlight the array not including the location labels then specify row numbers (which correspond to the locaton lables).
Thanks again!