split col into mutiple cols

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have single col with 9000+ entries. What I am looking for is a formla that
will look similar to this: B1=a1 &","& a2 (combining the two with comma). I
have no problem with this but I am needing the formula to split this into
cols of no more than 300 rows. So ending result will be col B, Col C, etc. -
300 rows each from data in A1 Any ideas how I can get this done? I don't want
to spend day cutting and pasting.. :) Please help.
 
Do you mean Data>Text to columns from the main menu?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Jack,

This is not complete. You want A1,A2 in B1. But then what? A3, A4 in C1?
Where does it end? This can only go to column 256 (rows 255 and 256), where
Excel runs out of columns. Do we stop at row 300 (row 600)? Then what do
we do with the rest of column A?
 
Jack

Not sure which orientation you want.

Here are two macros. First one moves data across columns, second one snakes
the data from top to bottom across columns.

In either case enter a number in the inputbox that will give you 300 rows or
less.

30 columns will equal 300 rows if you have 9000 cells in column A.

Try on a copy of the worksheet.


Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub


Public Sub SplitToCols()
Dim NUMCOLS As Integer
Dim i As Integer
Dim colsize As Long
On Error GoTo fileerror

NUMCOLS = InputBox("Choose Final Number of Columns")
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
(NUMCOLS - 1)) / NUMCOLS)
For i = 2 To NUMCOLS
Cells((i - 1) * colsize + 1, 1).Resize(colsize, 1).Copy Cells(1, i)
Next i
Range(Cells(colsize + 1, 1), Cells(Rows.Count, 1)).Clear
fileerror:
End Sub


Gord Dibben Excel MVP
 
In cell B2 put the following formula

=OFFSET($A$1,ROW()*2-2+((COLUMN()-2)*300),0)&","&OFFSET($A$1,ROW()*2-1+((COL
UMN()-2)*300),0)

Then copy across to the range B1:AE300 or whatever column is necessary till
you have all the values you need
 
Ken,
Thank you! Your formula worked like a charm. Right after I finished that
project something similar popped on my desk. This time I need to 5 rows
seperated by commas instead of 2 (ie b1=a1&","&,a2&","&a3&","&a4&","&a5) same
300 row deal. I tried to modify your formula but am having difficulty
understanding your logic in particular the row and column commands. I did
serch to find additional info to no prevail. Can you help me once again? Also
a tidbit about whats happening in formula would be greatly appreciated.
Thanks again!
Jack
 
Dohhhh - Just realised in fact that the 300s in the original post should
have been 600s in order to give you a table 300 rows deep - apologies.

Anyway, fixed for this one, so again put it in B2 and then copy and paste to
a 300 row by x column range

=OFFSET($A$1,ROW()*5-5+((COLUMN()-2)*1500),0)&","&OFFSET($A$1,ROW()*5-4+((CO
LUMN()-2)*1500),0)&","&OFFSET($A$1,ROW()*5-3+((COLUMN()-2)*1500),0)&","&OFFS
ET($A$1,ROW()*5-2+((COLUMN()-2)*1500),0)&","&OFFSET($A$1,ROW()*5-1+((COLUMN(
)-2)*1500),0)

Basically the OFFSET function allows you to set an anchor cell and then grab
a value from another cell x rows and y columns away from that anchor cell (0
rows and 0 columns away would be the anchor cell itself). From there you
simply set A1 as the anchor cell and then use the ROW() function to try and
get a value x rows away. What you look to do in this one is to get the
value from 0 rows away, 1 row away, 2 rows away, 3 rows away and 4 rows away
(5 row range).

The ROW() function returns the row number of the row itself, so by using it
5 times in the same cell but making an adjustment each time with -5, -4, -3
etc you get the following:-

In any cell in row 1 ROW() gives you 1, so therefore

ROW()*5 = 5

so therefore

ROW()*5-5 = 0 (0 rows offset from A1 = A1)
ROW()*5-4 = 1 (1 rows offset from A1 = A2)
ROW()*5-3 = 2 (2 rows offset from A1 = A3)
ROW()*5-2 = 3 (3 rows offset from A1 = A4)
ROW()*5-1 = 4 (4 rows offset from A1 = A5) which has now pulled in all
the values from the cells A1:A5

and when you then copy this to the next cell in row 2, then

ROW()*5 = 10

so therefore

ROW()*5-5 = 5 (5 rows offset from A1 = A6)
ROW()*5-4 = 6 (6 rows offset from A1 = A7)
ROW()*5-3 = 7 (7 rows offset from A1 = A8)
ROW()*5-2 = 8 (8 rows offset from A1 = A9)
ROW()*5-1 = 9 (9 rows offset from A1 = A10) which has now pulled in all
the values from the cells A6:A10

and so on.

The same logic used with the COLUMN function allows me to restart the
numbering at whatever multiple I want. If you are combining 2 values a time
then obviously in 300 rows you will capture 600 values, or the values from
the first 600 rows. If you want to combine values from 5 cells a time then
in 300 rows you will cover 5*300 = 1500 values.

Hope this helps somewhat
 
Back
Top