Merge from a table

[3Suk]

dear all,
I have below table :
====================
W X Y Z
A 1 0 1 3
B 0 0 0 2
====================
If I need to merge the column & row header like below :
=======
A-W 1
A-X 0
A-Y 1
A-Z 3
B-W 0
B-X 0
B-Y 0
B-Z 2
=======
How can I do it?

Thanks in advance,
Patrick.

 

[lhkittle]

dear all,

I have below table :

====================

W X Y Z

A 1 0 1 3

B 0 0 0 2

====================

If I need to merge the column & row header like below :

=======

A-W 1

A-X 0

A-Y 1

A-Z 3

B-W 0

B-X 0

B-Y 0

B-Z 2

=======

How can I do it?



Thanks in advance,

Patrick.

Hi Patrick,

I4&"-"&J3&OFFSET(I10,-6,1)
I4&"-"&K3&OFFSET(I11,-7,2)
I4&"-"&L3&OFFSET(I12,-8,3)
I4&"-"&M3&OFFSET(I13,-9,4)
I5&"-"&J3&OFFSET(I14,-9,1)
I5&"-"&K3&OFFSET(I15,-10,2)
I5&"-"&L3&OFFSET(I16,-11,3)

Where:
I4 & I5 = A & B.

J3, K3, L3, M3 = W X Y Z

J4:M5 Hold the values as per your example.

I10:I17 hold the formulas above. Copy and paste into that range and enter an "=" in front of each formula.

HTH
Regards,
Howard

 

[lhkittle]

dear all,

I have below table :

====================

W X Y Z

A 1 0 1 3

B 0 0 0 2

====================

If I need to merge the column & row header like below :

=======

A-W 1

A-X 0

A-Y 1

A-Z 3

B-W 0

B-X 0

B-Y 0

B-Z 2

=======

How can I do it?



Thanks in advance,

Patrick.

Patrick,

Just as a PS to my previous suggestion, if your table is really large whichI suspect it may be, you can use some out-of-the-way helper columns and cells and make cell references to these helpers in the formulas. This will allow you to use absolute references in the formulas and therefore pull themdown, saving a ton hard coding within the formulas.

Regards,
Howard

 

[3Suk]

Patrick,



Just as a PS to my previous suggestion, if your table is really large which I suspect it may be, you can use some out-of-the-way helper columns and cells and make cell references to these helpers in the formulas. This willallow you to use absolute references in the formulas and therefore pull them down, saving a ton hard coding within the formulas.



Regards,

Howard

Howard,
will try your suggestion and update the status later.
Thanks,
PL.