Recreate the upper part of a symetric data matrix 75x75
 

hi,

I have to use a matrix that come from SPSS. I get a 75x75 symetric
matrix of distances between points (75 points). Zero are in the
diagonal. Only the lower part of the diagonal is diplayed. I exported
this matrix in Excel.

I am trying to recreate the full matrix (I need this for special
calculus). Is there a way to automate this (I would like to avoid
copy-past transpose, in order to avoid errors) ?

Point X12 should have the same value of X21, same applies for X13 and
X31, etc.

Any idea ? Thks,

Michael

I would think that the TRANSPOSE array function would work for this. Use
Excel Help to find out more about the TRANSPOSE function.

Barb Reinhardt

"Michael Cantinotti" wrote:

hi,

I have to use a matrix that come from SPSS. I get a 75x75 symetric
matrix of distances between points (75 points). Zero are in the
diagonal. Only the lower part of the diagonal is diplayed. I exported
this matrix in Excel.

I am trying to recreate the full matrix (I need this for special
calculus). Is there a way to automate this (I would like to avoid
copy-past transpose, in order to avoid errors) ?

Point X12 should have the same value of X21, same applies for X13 and
X31, etc.

Any idea ? Thks,

Michael

Thank you for your reply Barb,

The problem with TRANSPOSE is that it is problematic to apply within a
triangular dataset.

E.g.:

0
2 0
3 2 0
1 5 3 0

If I have to recover the upper part of the matrix (above the 0s), I will
have to paste the function in each cell... I think I need something in
VBA. Maybe someone as already been faced with this problem ?

Michael

Barb R. wrote:
I would think that the TRANSPOSE array function would work for this. Use
Excel Help to find out more about the TRANSPOSE function.

Barb Reinhardt

"Michael Cantinotti" wrote:


hi,

I have to use a matrix that come from SPSS. I get a 75x75 symetric
matrix of distances between points (75 points). Zero are in the
diagonal. Only the lower part of the diagonal is diplayed. I exported
this matrix in Excel.

I am trying to recreate the full matrix (I need this for special
calculus). Is there a way to automate this (I would like to avoid
copy-past transpose, in order to avoid errors) ?

Point X12 should have the same value of X21, same applies for X13 and
X31, etc.

Any idea ? Thks,

Michael

Did you try TRANSPOSE with what would be the whole 75x75 array? What did
that do?

"Michael Cantinotti" wrote:

Thank you for your reply Barb,

The problem with TRANSPOSE is that it is problematic to apply within a
triangular dataset.

E.g.:

0
2 0
3 2 0
1 5 3 0

If I have to recover the upper part of the matrix (above the 0s), I will
have to paste the function in each cell... I think I need something in
VBA. Maybe someone as already been faced with this problem ?

Michael

Barb R. wrote:
I would think that the TRANSPOSE array function would work for this. Use
Excel Help to find out more about the TRANSPOSE function.

Barb Reinhardt

"Michael Cantinotti" wrote:


hi,

I have to use a matrix that come from SPSS. I get a 75x75 symetric
matrix of distances between points (75 points). Zero are in the
diagonal. Only the lower part of the diagonal is diplayed. I exported
this matrix in Excel.

I am trying to recreate the full matrix (I need this for special
calculus). Is there a way to automate this (I would like to avoid
copy-past transpose, in order to avoid errors) ?

Point X12 should have the same value of X21, same applies for X13 and
X31, etc.

Any idea ? Thks,

Michael

Hi barb,

Just to tell you that I found a solution. It was so easy.... I am quite
shameful ;)

Just have to select the table (only the data below the diagonal are
entered in that table. The upper part is empty). Then, copy and paste
the transposed table in another sheet. Eventually, copy the transposed
table WITHOUT blank cells (option in paste special) on the original
table.

I am so happy it works....

Regards,

Michael

Barb R. wrote:
Did you try TRANSPOSE with what would be the whole 75x75 array?
What did
that do?

"Michael Cantinotti" wrote:

Thank you for your reply Barb,

The problem with TRANSPOSE is that it is problematic to apply
within a
triangular dataset.

E.g.:

0
2 0
3 2 0
1 5 3 0

If I have to recover the upper part of the matrix (above the 0s), I
will
have to paste the function in each cell... I think I need something
in
VBA. Maybe someone as already been faced with this problem ?

Michael

Barb R. wrote:
I would think that the TRANSPOSE array function would work for
this. Use
Excel Help to find out more about the TRANSPOSE function.

Barb Reinhardt

"Michael Cantinotti" wrote:


hi,

I have to use a matrix that come from SPSS. I get a 75x75
symetric
matrix of distances between points (75 points). Zero are in the
diagonal. Only the lower part of the diagonal is diplayed. I
exported
this matrix in Excel.

I am trying to recreate the full matrix (I need this for special
calculus). Is there a way to automate this (I would like to avoid

copy-past transpose, in order to avoid errors) ?

Point X12 should have the same value of X21, same applies for X13
and
X31, etc.

Any idea ? Thks,

Michael