Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Split mutiple columns in sets of 40 [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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.

Please help

See more

2 replies

Answer 1 / 2
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 763
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
Answer 2 / 2

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. :)
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 763
and macro is no acceptable way of doing it ?
Report

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.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 763
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
Report

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.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 763
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.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!