copy vertical values from a column across a rows

A

Ash

Hi,
I have got a 150 x 150 cell spreadsheet with values on one side of the
diagonal of the square matrix. So that, one right triangle has values in all
cells, while the other right triangle is empty, across a diagonal. I need to
copy the column value into contrasting row from populated cells to empty
cells, so that the two right triangles become mirror image. For example,

values A B C D E
A 0
B 0.24 0
C 0.35 0.86 0
D 0.64 0.27 0.74 0
E 0.57 0.46 0.84 0.43 0

Desired output

values A B C D E
A 0 0.24 0.35 0.64 0.57
B 0.24 0 0.86 0.27 0.46
C 0.35 0.86 0 0.74 0.84
D 0.64 0.27 0.74 0 0.43
E 0.57 0.46 0.84 0.43 0

How can I achieve it?
Please help.
Thanks in advance.
 
B

Bob Umlas, Excel MVP

If your data starts in A1, Select A1, use edit/goto special, select
currentregion, OK, then edit/goto special, select blanks, click OK.
Now ctrl/enter this formula =INDEX($A$1:$E$150,COLUMN(),ROW())
(not ctrl/shift/enter)
 
Top