software

Maybe you guys could take a break from giving free technical school
answers to lazy partiers and try this fun problem. I dreamed part of
it last nicht.

How do I transpose an array of words and numbers into an array of
different proportions?

The proportions of the arrays can be anything, including n x 1 and 1 x
n.
The size of the array can be anything that fits on a worksheet and can
be located anywhere on the worksheet.

Ex:
Array of the present (4x5)
ab 12 de 1.55 ghe
ef 34 nu 3.65 unt
gh 56 mc 2.45 wen
kl 78 vm 1.35 rep

After a re-arrange (7x3)
ab 12 de 1.55 ghe ef 34
nu 3.65 unt gh 56 mc 2.45
wen kl 78 vm 1.35 rep

I am uncomfortable with VBA so don't use VBA.

I don't want to copy/paste because I want the second array to update
immediately when I change the values in the first array.

Also another reason I don't want to use VBA is I would necessitate to
run it after every update.

Thank you and have a good evening,
Manfred Straub
(originally from east Zurich)Here's one formulas play which could achieve this ..

A sample construct is available at:
cjoint.com/?cihstRPtdA
Transform a source matrix into another matrix of a different size.xls

The source 5C x 4R matrix is assumed in A1:E4

Put in say, G1:
=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/5),MOD(ROWS($A$1:A1)-1,5))
Copy G1 down to G20, to re-lay the source matrix into a vert col (1C x 20R)
(quot;5quot; = # no. of cols)

Then put in say, J2:
=IF(ISERROR(INDEX($G$1:$G$20,ROW(A1)*7-7 COLUMN(A1))),quot;quot;,
INDEX($G$1:$G$20,ROW(A1)*7-7 COLUMN(A1)))
Copy J2 across amp; fill down to P3 populate the desired 7C x 3R matrix, which
would be dynamic to changes in the source matrix
(quot;7quot; = # no. of cols)

Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Manfredquot; gt; wrote in message oups.com...
gt; Maybe you guys could take a break from giving free technical school
gt; answers to lazy partiers and try this fun problem. I dreamed part of
gt; it last nicht.
gt;
gt; How do I transpose an array of words and numbers into an array of
gt; different proportions?
gt;
gt; The proportions of the arrays can be anything, including n x 1 and 1 x
gt; n.
gt; The size of the array can be anything that fits on a worksheet and can
gt; be located anywhere on the worksheet.
gt;
gt; Ex:
gt; Array of the present (4x5)
gt; ab 12 de 1.55 ghe
gt; ef 34 nu 3.65 unt
gt; gh 56 mc 2.45 wen
gt; kl 78 vm 1.35 rep
gt;
gt; After a re-arrange (7x3)
gt; ab 12 de 1.55 ghe ef 34
gt; nu 3.65 unt gh 56 mc 2.45
gt; wen kl 78 vm 1.35 rep
gt;
gt; I am uncomfortable with VBA so don't use VBA.
gt;
gt; I don't want to copy/paste because I want the second array to update
gt; immediately when I change the values in the first array.
gt;
gt; Also another reason I don't want to use VBA is I would necessitate to
gt; run it after every update.
gt;
gt; Thank you and have a good evening,
gt; Manfred Straub
gt; (originally from east Zurich)
gt;
Typos in line:
gt; Copy J2 across amp; fill down to P3 populate the desired 7C x 3R matrix

should read as:
gt; Copy J2 across amp; fill down to P4 to populate the desired 7C x 3R matrix
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Max,

Your example seems to work, but when I insert a row
at 1 or a column at F, the 3x7 array goes kaputt.
Anyway, I was looking to create the 3x7 array without
creating the 15x1 array frist.

Manfred Straubquot;Manfredquot; wrote :
gt; Your example seems to work, but when I insert a row
gt; at 1 or a column at F, the 3x7 array goes kaputt.

Slightly more robust (but still not foolproof) ..

Define the range G1:G20 as: VertC (say)

Then put instead in J2:
=IF(ISERROR(INDEX(VertC,ROWS($A$1:A1)*7-7 COLUMNS($AA$1:AA1))),quot;quot;,INDEX(Vert
C,ROWS($A$1:A1)*7-7 COLUMNS($AA$1:AA1))
and copy J2 across/fill down to P4, as before

gt; Anyway, I was looking to create the 3x7 array without
gt; creating the 15x1 array first.

I'm not sure if this is possible, and with quot;insert row/column-proofingquot;
thrown in as well lt;ggt;. Let's hang around awhile for possible insights from
others.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
VertC could also be cut-out and pasted in another sheet,
and the sheet hidden away.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---