Excel Matrix problem

C

Catalin

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
 
N

NoodNutt

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

Lars-Åke Aspelin

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

Bernd P

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
 
Top