Excel Matrix problem
 

Dear friends,

I have a matrix like the one shown below

3 4 5 6 7 8
30x20 1.11 1.45
40x20 1.35 1.77
45x30 2.25 2.77
50x30 2.96
50x40 2.71 3.35
60x30 3.37 3.98 4.59
60x40 3.76 4.46 5.14
65x50 4.35 5.97
75x50 4.74 6.51
75x55 4.95 6.80
80x40 5.41 7.07
80x65 6.60 8.66


There are 2 axis: on horizontal (3, 4, 5...8) and on vertical
(30x20....80x65). These two represents the lengths of the sides of uneven hot
rolled angle and the horizontal is the thickness of the material. In the
middle of the table I have the unitary weight for corresponding angle: ex.
30x20x3 (the angle sides x thick) has 1.11 kgs/linear meter.
This actually look very much like a classic Pivot Table.

Now, all what I need is to transform this "Pivot" into the original table
which I think that should look like this:

30x20 3 1.11
30x20 4 1.45
30x20 5 0
..
..
..
30x20 8 0
40x20 3 1.35
40x20 4 1.77
..
..
..

80x65 8 8.66

Is there any way to do it like a reverse Pivot or is there any formula for it?
If not, I think I need some VBA code...

I use Excel 2003.

Thank you in advance

Excel Matrix problem
 

G'day Catalin

Looks more likely an Array would suit your needs better.

Send me your e-mail address & I will forward you a small example file for
you to look at.

HTH
Mark.

Excel Matrix problem
 

Oops!!!!!

Try sending it to:



Mark.

Excel Matrix problem
 

On Tue, 18 Nov 2008 06:12:01 -0800, Catalin
wrote:

Dear friends,

I have a matrix like the one shown below

3 4 5 6 7 8
30x20 1.11 1.45
40x20 1.35 1.77
45x30 2.25 2.77
50x30 2.96
50x40 2.71 3.35
60x30 3.37 3.98 4.59
60x40 3.76 4.46 5.14
65x50 4.35 5.97
75x50 4.74 6.51
75x55 4.95 6.80
80x40 5.41 7.07
80x65 6.60 8.66


There are 2 axis: on horizontal (3, 4, 5...8) and on vertical
(30x20....80x65). These two represents the lengths of the sides of uneven hot
rolled angle and the horizontal is the thickness of the material. In the
middle of the table I have the unitary weight for corresponding angle: ex.
30x20x3 (the angle sides x thick) has 1.11 kgs/linear meter.
This actually look very much like a classic Pivot Table.

Now, all what I need is to transform this "Pivot" into the original table
which I think that should look like this:

30x20 3 1.11
30x20 4 1.45
30x20 5 0
.
.
.
30x20 8 0
40x20 3 1.35
40x20 4 1.77
.
.
.

80x65 8 8.66

Is there any way to do it like a reverse Pivot or is there any formula for it?
If not, I think I need some VBA code...

I use Excel 2003.

Thank you in advance


Assuming your horizontal axis is on row 1, from column B to column G,
and your vertical axis is in column A, from row 2 to row 13,
you can try the following:

In cell H1:
=OFFSET(A$2,(ROW()-1)/6,0)

In cell I1:
=OFFSET(A$1,0,1+MOD(ROW()-1/6))

In cell J1:
=OFFSET(A$1,1+(ROW()-1)/6,1+MOD(ROW()-1,6))

Copy cells H1, I1 and J1 down until row 72 to get the entire table.

If you want to remove the original matrix you can copy columns H,I,J
and Paste Special the values to another location of your choice.

Hope this helps / Lars-Åke

Excel Matrix problem
 

Hello,

Or with a VBA function:
Function ap(vI As Variant) As Variant
Dim i As Long, j As Long, k As Long
ReDim vR(1 To (vI.Rows.Count -1)* (vI.Columns.Count -1), _
1 To 3) As Variant
k = 1
For i = 2 To vI.Rows.Count
For j = 2 To vI.Columns.Count
vR(k, 1) = vI(i, 1)
vR(k, 2) = vI(1, j)
vR(k, 3) = vI(i, j)
k = k + 1
Next j
Next i
ap = vR
End Function

Select a sufficient long range of rows (row count - 1 * column count -
1 of your input array) with 3 columns and array-enter =ap(A1:G13) if
your input data is in A1:G13.

Regards,
Bernd