# Merge from a table

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?

Patrick.

L

#### 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?

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

L

#### 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?

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

3

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