Data Manipulation FUNCTION

D

daufoi

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?
 
D

duane

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
 
M

mangesh_yadav

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
 
H

Harlan Grove

daufoi said:
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
Click to expand...
....

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].
 
D

daufoi

thanks a million Harlan Grove! I did some tweaking but your function i
at the heart of it
 
Top