PDA

View Full Version : Solved: Split large data into various column



anandbohra
08-24-2007, 06:37 AM
Hi all

pl help me.
I have 20 data in cells from range a1 to a20
i want a loop which ask user to input break point
& then after every break point transfer data in next column

e.g. if user say 5 (just example will take care for no of columns)
then data presentation will be as follows
columns A--b--c--d
1--6--11--16
2--7--12--17
3--8--13--18
4--9--14--19
5--10--15--20

in this way (20 is just for example but in actual it always near 5000 & have to split in 90-100 in one column)

hope u r clear with my query

daniel_d_n_r
08-24-2007, 06:40 AM
Is your user a programmer?
I doubt your average user will utilise this sort of thing.

anandbohra
08-24-2007, 06:48 AM
no actually the file which comes from database will come in say 1000-5000 records & has to spilt between 3-4 user (purely dataentry operator) so what i am doing is just spliting the data in columns as per my wish & then instruct them to feed the other information so every one has share of average > 250 records.

that is why i want a loop which prompt me for no of records after which data should move to next column thats it

Bob Phillips
08-24-2007, 07:33 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim iLastRow As Long
Dim mpSplit As Long
Dim mpAdd As Long

With ActiveSheet

mpSplit = InputBox("Input split value")
If mpSplit <> 0 Then

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
mpAdd = iLastRow Mod mpSplit
If mpAdd <> 0 Then mpAdd = mpAdd + 1
For i = iLastRow + mpAdd To 5 Step -mpSplit
For j = 1 To mpSplit - 1
.Cells(i - 4, TEST_COLUMN).Offset(0, j).Value = _
.Cells(i - (mpSplit - j) + 1, TEST_COLUMN).Value
Next j
.Rows(i - 3).Resize(mpSplit - 1).Delete
Next i
End If
End With

End Sub

anandbohra
08-26-2007, 10:07 PM
thanks xld but the data format is not as per my requirement

i typed 1-10 in A1 to a10 & say through split box user prompt for 5 (enter split value) now what i want is data split as 1-5 in a1 to a5 & balance 6-10 in b1 to b5.
but current code split as a1 to e1 shows 1-5 & a2 to e2 shows balance 6-10.
hope u r clear now.


pl provide amended code.

p45cal
08-27-2007, 03:40 AM
trySub blah()
TestCol = "A"
Dim RngToSplit As Range
lastrow = Range(TestCol & "1").End(xlDown).Row
Set RngToSplit = Range(TestCol & "1", TestCol & lastrow)
mpSplit = CLng(InputBox("Input split value"))
mycol = Range(TestCol & "1").Column
For rw = 1 To lastrow Step mpSplit
If (lastrow - rw) < mpSplit Then 'tests for LAST block to be cut'n'pasted
Range(TestCol & rw, TestCol & lastrow).Cut Range(Cells(1, mycol), Cells(1, mycol))
Else
Range(TestCol & rw, TestCol & rw + mpSplit - 1).Cut Range(Cells(1, mycol), Cells(1, mycol))
End If
mycol = mycol + 1
Next rw
End Sub

Bob Phillips
08-27-2007, 04:18 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim iLastRow As Long
Dim mpSplit As Long
Dim mpAdd As Long

With ActiveSheet

mpSplit = InputBox("Input split value")
If mpSplit <> 0 Then

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
mpAdd = iLastRow Mod mpSplit
If mpAdd <> 0 Then mpAdd = mpSplit - mpAdd
For i = iLastRow + mpAdd To 6 Step -mpSplit
.Columns(2).Insert
For j = 1 To mpSplit
.Cells(1, TEST_COLUMN).Offset(j - 1, 1).Value = _
.Cells(i - (mpSplit - j), TEST_COLUMN).Value
Next j
.Rows(i - 4).Resize(mpSplit).Delete
Next i
End If
End With

End Sub

anandbohra
08-27-2007, 05:27 AM
thanks p45cal

your code works.


BUT unfortunately
Dear XLD
this time your code is not giving proper output