Recreate the upper part of a symetric data matrix 75x75

[Michael Cantinotti]

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

[Barb R.]

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
>

[Michael Cantinotti]

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
>>

[Barb R.]

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
> >>
>

[chantelanuit]

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
> > >>
> >