Data Manipulation FUNCTION

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

[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

[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

[Harlan Grove]

"daufoi" <[email protected]> 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].

[daufoi]

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

[Harlan Grove]

"daufoi" <[email protected]> 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].

[Harlan Grove]

"daufoi" <[email protected]> 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].

[Harlan Grove]

"daufoi" <[email protected]> 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].

[Harlan Grove]

"daufoi" <[email protected]> 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].

[Harlan Grove]

"daufoi" <[email protected]> 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].

[Harlan Grove]

"daufoi" <[email protected]> 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].