arraysexcelmultidimensional-arraymultiple-columnsreorganize

Move Columnar Data to Correlation Matrix

I was looking for a solution (avoiding vba) to reorganize 3 columns representing data1, data2, and f(data1,data2) into a 2d array.

The data is as follows:

X1  X2  Z_1_2
X1  X3  Z_1_3
X1  X4  Z_1_4
X1  X5  Z_1_5
X2  X3  Z_2_3
X2  X4  Z_2_4
X2  X5  Z_2_5
X3  X4  Z_3_4
X3  X5  Z_3_5
X4  X5  Z_4_5

Edit: I will give some more details...

X are strings and Z are numerics.

The results should be as following:

     X1     X2     X3     X4     X5
X1   1      Z_1_2  Z_1_3  Z_1_4  Z_1_5
X2   Z_1_2  1      Z_2_3  Z_2_4  Z_2_5
X3   Z_1_3  Z_2_3  1      Z_3_4  Z_3_5
X4   Z_1_4  Z_2_4  Z_3_4  1      Z_4_5
X5   Z_1_5  Z_2_5  Z_3_5  Z_4_5  1

Thanks !!!

Solution

  • So you need to set up the headers, correctly both the row and column headers.

    enter image description here

    Then if the Upper left cell put this formula:

    =IFERROR(INDEX($C:$C,MATCH($G2&"|"&H$1,$A:$A&"|"&$B:$B,0)),IFERROR(INDEX($C:$C,MATCH(H$1&"|"&$G2,$A:$A&"|"&$B:$B,0)),1))

    This is an array formula and needs to be confirmed with Ctrl-Shift-Enter instead of just enter. Copy and fill the rest of the array.

    enter image description here

    One warning this will take time to compute, It can be sped up by limting the full column references to only those with data; $C:$C in this instance would be $C$1:$C$10.