Macro to Split Up a Column

[Mister_J]

I could really use some help creating a macro. I am still a beginner when it comes to creating macros but I have managed to piece a couple together with the record function and making some tweaks here and there. But what I’m asking for is WAY over my head.

What I’d like to create is a command that will take a column of numbers, and divide them up into separate columns based on a number that I provide.

Example:
If I have a spreadsheet with 500 numbers in column A and I would like to split that up into 5 columns of 100 numbers (i.e. 100 in column B, 100 in column C, 100 in column D…….) or if I wanted to have 10 columns of 50 (i.e. 50 in column B, 50 in column C, 50 in column D…….) this is what I’m looking for.

So with all that explained, my first question is whether it is possible? And my second is how I would even start to create something like this? I know it is a very tall order so if no one can help I totally understand. But if anyone can point me in the right direction I would be super grateful. Thanks for your time.

 

[Special-K99]

Input from Sheet1, output to Sheet 2
Sheet!!B1 to contain number of columns required (rows is irrelevant)

'
'   Convert Sheet1 single column to table with width k
'
Sub SingleColumnWidth()
k = Worksheets("Sheet1").Cells(1, 3)
m = 0
n = 1
Application.ScreenUpdating = False
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Lastrow
m = m + 1
Worksheets("Sheet2").Cells(n, m) = Worksheets("Sheet1").Cells(i, 1)
If m = k Then m = 0: n = n + 1
Next i
Application.ScreenUpdating = True
End Sub

 

[Mister_J]

Special-K99 Thank you very much for your help. Unfortunately I am having a little trouble figuring out the process your code is setting up. I created a blank book with a second sheet, and in sheet1 I filled column A with 30 numbers and in B1 I put 3 expecting column A B and C to have 10 numbers in them but when I ran the macro it seems to have just transposed the numbers on sheet2. I may be missing a step but I'm not sure what it is.

Just an FYI in case it explains my desire a little better, the reason I would like to create the macro is at my work I have to compare records in multiple systems but most of the systems have a character limit. So I may have have a list of 700 numbers but one of the systems will only take 75 numbers at a time while the next system will take 100 and so on. so I'm just looking for an easier way to split them up rather than copy and pasting different groups all the time.

I'll keep tinkering with what you gave me so far. Thanks again!

 

[Mister_J]

I figured it out!!!!!! The number of columns I want on the output on sheet2 needs to be in C1 on sheet1. Is there any chance I can make an additional request? Now that I got it to work (Thank you once again) is there a way that you can make it output in a horizontal order.

currently it's outing like this:
1 2 3 4
5 6 7 8
9 10 11 12

Can it be done like this:
1 5 9
2 6 10
3 7 11
4 8 12

And a bonus would be if I could input the value of rows. Like I would input the maximum number of values I can have in one column and it would just drop to the next column of that column exceeded the number i provided.

Example of bonus when I input 6 with a list of 23:
1 7 13 19
2 8 14 20
3 9 15 21
4 10 16 22
5 11 17 23
6 12 18

I know I'm asking for a lot so any and all help is appreciated!!!

 

[Special-K99]

Sorry I forgot to say I'd chosen cell C1 as the table width (length now).
in the code just change this

Worksheets("Sheet2").Cells(n, m) = Worksheets("Sheet1").Cells(i, 1)

to this

Worksheets("Sheet2").Cells(m, n) = Worksheets("Sheet1").Cells(i, 1)

The code works by (let's assume the new amendment has been made so it outputs by length)
n is set to 1 the first column to output the data to. m is the row number to output data to.
It then loops through column A. As it runs through column A it adds 1 to m the row number and places the output from column A in cell (m,n)
Having done this it checks current row number m against the specified length of the table k. If that length has been reached then it resets row number m back to 0 and increases the column number n otherwise

 

[Mister_J]

You Sir, are amazing! That is exactly what I was looking for. Thank You! Also thanks for the explanation, a lot of the time when I search for macros or attempt to create them I don't completely understand what functions the code is running so when I'm trying to make adjustments it's purely guess work.