Transpose and link Multicolum matrix in 2 colum array
 

Hi
I have a matrix of the format
ny chicago beijing ...
manager 1 2 3
staff 101 200 300
Volunteer 102 202 302

and I want to link it to a single column data
manager ny 1
staff ny 101
Volunteer ny 102
manager chicago 2
staff chicago 200
Volunteer chicago 202

where the 2nd table is linked to the first and whenever the data changes it
in the first it changes also in the second

Transpose and link Multicolum matrix in 2 colum array
 

One play to try ..

Assuming the source matrix is in Sheet1, A1:D4
ny chicago beijing ...
manager 1 2 3
staff 101 200 300
Volunteer 102 202 302

In a new Sheet2, place

In A1:
=INDEX(Sheet1!A:A,MOD(ROW(A1)-1,3)+2)

In B1:
=INDEX(Sheet1!$1:$1,,INT((ROW(A1)-1)/3)+2)

In C1:
=OFFSET(Sheet1!$B$2,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3))

then select A1:C1 and fill down to C9

Sheet2 will auto-return the required results
(dynamic to Sheet1's source matrix)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"stratis" wrote in message
...
Hi
I have a matrix of the format
ny chicago beijing ...
manager 1 2 3
staff 101 200 300
Volunteer 102 202 302

and I want to link it to a single column data
manager ny 1
staff ny 101
Volunteer ny 102
manager chicago 2
staff chicago 200
Volunteer chicago 202

where the 2nd table is linked to the first and whenever the data changes
it
in the first it changes also in the second