Merge from a table

  • Thread starter Thread starter 3Suk
  • Start date Start date
3

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

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

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

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