Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Manipulation FUNCTION
I am interested in making this: 1 2 3 4 5 6 7 8 9 10 11 12 into this: 1 5 9 2 6 10 3 7 11 4 8 12 However, I need something that is flexible. For example, sometimes I will need to make 3 columns of 4 rows and sometimes I will need 2 of 6. Sometimes I will have many more columns and rows. In other words, the data will take on different shapes and sizes. Therefore, something specific to the cells (ie in a macro) is only useful once. Therefore, anyone know of a function that I can use to manipulate data in this way? -- daufoi ------------------------------------------------------------------------ daufoi's Profile: http://www.excelforum.com/member.php...o&userid=23911 View this thread: http://www.excelforum.com/showthread...hreadid=376172 |
#2
|
|||
|
|||
this has some flexibility - i did assume the column of data started in cell a1 but that could be changed too. Option Explicit Sub Macro1() Dim i As Long Dim j As Long Dim data(100000) Dim rows As Integer Dim cols As Integer Dim outputcol As Integer Dim outputrow As Integer ' 'define range names on sheet for row and column input, 'and output location ' rows = Range("rows").Value cols = Range("columns").Value outputcol = Range("Output").Column outputrow = Range("output").Row 'clear prevous output Range(Cells(outputrow + 1, outputcol), Cells(outputrow + 100, _ outputcol + 100)).ClearContents For i = 1 To Cells(1, 1).End(xlDown).Row data(i) = Cells(i, 1).Value Next i For i = 1 To rows For j = 0 To cols - 1 Cells(outputrow + i, outputcol + j) = data(i + j * rows) Next j Next i End Sub -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=376172 |
#3
|
|||
|
|||
Hi daufoi, I had replied to this query of yours on another thread: http://excelforum.com/showthread.php?t=374965 Here's the solution again: sub RuntThis() Call myArrange(4, 3) End Sub Sub myArrange(rows, columns) Set rng = Range("A1:A12") For i = 1 To rng.Count If i Mod rows = 0 Then r = rows If i Mod rows < 0 Then r = i Mod rows If i Mod rows = 0 Then c = Int(i / rows) If i Mod rows < 0 Then c = Int(i / rows) + 1 temp = rng(i, 1) rng(i, 1).Clear rng(r, c) = temp Next i End Sub Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=376172 |
#4
|
|||
|
|||
"daufoi" wrote...
I am interested in making this: 1 2 3 4 5 6 7 8 9 10 11 12 into this: 1 5 9 2 6 10 3 7 11 4 8 12 .... If the upper list were named List, you could create the lower array in, say, C3:E6 by selecting C3:E6, typing the formula =OFFSET(List,MOD(ROW()-ROW($C$3),4)+(COLUMN()-COLUMN($C$3))*4,0,1,1) and pressing [Ctrl]+[Enter]. |
#5
|
|||
|
|||
thanks a million Harlan Grove! I did some tweaking but your function is at the heart of it. -- daufoi ------------------------------------------------------------------------ daufoi's Profile: http://www.excelforum.com/member.php...o&userid=23911 View this thread: http://www.excelforum.com/showthread...hreadid=376172 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
clock | Excel Worksheet Functions | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
Load active cell data via a function | Excel Worksheet Functions |