Transpose words and numbers into array of different proportions

  • Thread starter Thread starter Manfred
  • Start date Start date
M

Manfred

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:
http://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)
("5" = # no. of cols)

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

Adapt to suit ..
 
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 Straub
 
Your example seems to work, but when I insert a row
at 1 or a column at F, the 3x7 array goes kaputt.
Click to expand...

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))),"",INDEX(Vert
C,ROWS($A$1:A1)*7-7+COLUMNS($AA$1:AA1))
and copy J2 across/fill down to P4, as before
Anyway, I was looking to create the 3x7 array without
creating the 15x1 array first.
Click to expand...

I'm not sure if this is possible, and with "insert row/column-proofing"
thrown in as well <g>. Let's hang around awhile for possible insights from
others.

---
 
Back
Top