# Split mutiple columns in sets of 40

Solved/Closed
vikki - May 5, 2012 at 08:00 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 12, 2012 at 06:51 PM
Hello,

I have data in 3 columns A,B,C and it goes to 1000 rows, I want it to be splitted in sets of 40 each . Like first 40 then next 40 and so on.
Each set needs to have data of all the three columns

I tried to use =OFFSET(\$A\$1,COLUMNS(\$A:A)*40-41+ROWS(\$1:1),) But this applies to a single column.
As of now i combine data from all three column in a single then break it up in a set of 40 and again split it using text to column which is tiring.

## 2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 6, 2012 at 03:58 PM
what do you mean by "split in set of 40"
COuld you post a sample workbook at some file sharing site and post back the link to the file back here for better understanding
hmmm what i mean by splitting in sets of 40 entries can be understood as under

this is sample data in three columns with 20 rows
A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
A6 B6 C6
A7 B7 C7
A8 B8 C8
A9 B9 C9
A10 B10 C10
A11 B11 C11
A12 B12 C12
A13 B13 C13
A14 B14 C14
A15 B15 C15
A16 B16 C16
A17 B17 C17
A18 B18 C18
A19 B19 C19
A20 B20 C20

Now i want to arrange these 20 rows in sets of say 5 rows as under

A1B1C1 | A6B6C6 | A11B11C11 | A16 B16 C16
A2B2C2 | A7B7C7 | A12B12C12 | A17 B17 C17
A3B3C3 | A8B8C8 | A13B13C13 | A18 B18 C18
A4B4C4 | A9B9C9 | A14B14C14 | A19 B19 C19
A5B5C5 | A10B10C10 | A15B15C15 | A20 B20 C20

Similarly i want my three columns with a large number of rows in sets of 40
i hope i answer my situation correctly.
thanks for having a look in my query. :)
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 10, 2012 at 10:38 AM
and macro is no acceptable way of doing it ?
I can use macro not an issue but don't have a good hand in it so was trying to get it done easy way :)
can u help me with macros.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 10, 2012 at 06:48 PM
try this

```Public Sub distributeColumns()

Dim sTargetSheet           As String
Dim iNoOfColumnInBlock     As Integer
Dim iMaxRowsInBlock        As Integer
Dim lLastRow               As Long
Dim iNextBlock             As Integer
Dim rngMove                As Range
iNoOfColumnInBlock = 3
iMaxRowsInBlock = 5
sTargetSheet = "Sheet1"
iNextBlock = 1
With Sheets(sTargetSheet)
lLastRow = getItemLocation("*", .Cells)

Do While (lLastRow > iMaxRowsInBlock)
iNextBlock = iNextBlock + iNoOfColumnInBlock
Set rngMove = .Range(.Cells(iMaxRowsInBlock + 1, 1), .Cells(iMaxRowsInBlock * 2, iNoOfColumnInBlock))
rngMove.Copy
.Cells(1, iNextBlock).PasteSpecial
rngMove.Delete Shift:=xlUp
lLastRow = lLastRow - iMaxRowsInBlock
Loop
End With

End Sub
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long

'find the first/last row/column  within a range for a specific string

Dim Cell             As Range
Dim iLookAt          As Integer
Dim iSearchDir       As Integer
Dim iSearchOdr       As Integer

If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If

With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With

If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing

End Function

```
hi Riz this was really helpful... :)
Thanks a ton..... :)
Can you share me some tutorials i would love to learn more abt excel seems interesting.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 12, 2012 at 06:51 PM
you are welcome.
As far as tutorial is concern, I am sure there are many if you do a google search. The best tutorial I found was using the macro recorder to create a template code. In most case if gives you very good start.