Transpose and link Multicolum matrix in 2 colum array

S

stratis

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
 
M

Max

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
Click to expand...

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