matric values

bird_brain9

New Member
Joined
Oct 21, 2006
Messages
3
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

New Member
Joined
Oct 21, 2006
Messages
3
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
ABCDE
1location1234
21342392
32344756
43234775
54925675
Sheet1


Thanks for the help!
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
how about this sort of thing:
Book1
ABCDE
11234
21342392
32244756
43234775
54925675
6
7From2
8To4
9Distance56
Sheet3



formula is:

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

bird_brain9

New Member
Joined
Oct 21, 2006
Messages
3
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!
 

Forum statistics

Threads
1,136,616
Messages
5,676,831
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top