# matric values

#### bird_brain9

##### New Member
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

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

##### MrExcel MVP
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
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
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!

##### MrExcel MVP
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
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!

Replies
2
Views
191
Replies
3
Views
77
Replies
2
Views
119
Replies
3
Views
226
Replies
7
Views
291

### Forum statistics

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.

### Which adblocker are you using?

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

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