Transforming Data

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,
 
B

Bob Umlas

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
 
M

Murtaza

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
 
A

Alan Beban

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
 
H

Herbert Seidenberg

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
 
Top