Transforming Data

  • Thread starter Thread starter Murtaza
  • Start date Start date
M

Murtaza

Consider the the below example:

Sheet1: (This is what I have)
A B C D E F
1 x x x y y y
2 x x x y y y
3 x x x y y y
4 x x x y y y
5 x x x y y y

*Sheet2: (This is how I want it to be)
A B C
1 x x x
2 y y y
3 x x x
4 y y y
5 x x x
6 y y y
7 x x x
8 y y y
9 x x x
10 y y y
* Sheet2 links with Sheet1

Hope this illustrates my problem.....and I am sure you must have some
solution for it.

Thank you,
 
This works lightning quick:
Sub ReFlow()
n = 0
For Each x In Sheets("sheet1").Range("A1:F5")
n = n + 1
Sheets("Sheet2").Range("A1:C10").Cells(n).Value = x
Next
End Sub
 
Thanks Bob, It worked after some Range adjustments.

But Still it didn't provide the Links. Can't we do this by using Offset or
Indirect function.....cause Macro sometimes irritates.

Murtaza
 
Murtaza said:
Consider the the below example:

Sheet1: (This is what I have)
A B C D E F
1 x x x y y y
2 x x x y y y
3 x x x y y y
4 x x x y y y
5 x x x y y y

*Sheet2: (This is how I want it to be)
A B C
1 x x x
2 y y y
3 x x x
4 y y y
5 x x x
6 y y y
7 x x x
8 y y y
9 x x x
10 y y y
* Sheet2 links with Sheet1

Hope this illustrates my problem.....and I am sure you must have some
solution for it.

Thank you,
Click to expand...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
enter into A1:C10 on Sheet2

=ArrayReshape(Sheet1!A1:F5,10,3)

Alan Beban
 
In case Alan's excellent macro still irritates you,
here is a way to do it without VBA.
Name your input array Harry. Use Insert>Name>Define
Also define these names:
Rolk ={1;2;3;4;5;6;7;8;9;10}
Colk ={1,2,3}
Select your output array and enter into the formula bar
=INDEX(Harry,CEILING(Rolk/2,1),--NOT(MOD(Rolk,2))*3+Colk)
then press Shift+Ctrl+Enter
 
Back
Top