Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Transforming Data
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, |
#2
|
|||
|
|||
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 "Murtaza" <NoEmail@NoEmail wrote in message ... 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, |
#3
|
|||
|
|||
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 "Bob Umlas" wrote in message ... 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 "Murtaza" <NoEmail@NoEmail wrote in message ... 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, |
#4
|
|||
|
|||
Murtaza wrote:
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, 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 |
#5
|
|||
|
|||
In case Alan's excellent macro still irritates you,
here is a way to do it without VBA. Name your input array Harry. Use InsertNameDefine 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |