Excel Rearranging Cells in Excel 2007

[prose100]

I would like to rearrange some cells in Excel 2007. Here is what I would like to do:

Go from this:
1
22
31
46
5
6
75
8
...

To:

1 22 31 46
5 6 75 8
.....

Thanks in advance for any help you can give.

Paul

 

[alow]

the quick easy way, if it's just small selections like this, is to use Paste Special | Transpose. Select the first 4 cells and right click and choose Copy. Then select just the first cell and right click and choose Paste Special. Select "Transpose" and press OK. Repeat for other areas.

Let me know if that works for you.

 

[prose100]

Thanks for the quick response.

I would like to do this for a long column of data, so I would like to eliminate as much manual work as possible. Is there a more automated or programmatic way?

Thanks,
Paul

 

[alow]

I'm sure it could be done with vba, however, I'm no good at VBA. Let me think if I can come up with a formulaic way of doing this for you in the meantime. Are you looking to take them in groups of 4 and move them to 4 columns?

 

[prose100]

Yes, I would like to change the single column into 4 columns.

 

[Sky Tucker]

Do you want to split one column of data into four equal columns? If you can give a bit more info, I will write the code for you.

Cheers.

 

[prose100]

Yes, 4 equal columns. So the conversion would look like this:

1
2
3
4
5
6
7
8
9
10
11
12
.
.
.

1 2 3 4
5 6 7 8
9 10 11 12
....

So, a single column of 12 rows would convert to 4 columns of 3 rows.
A single column of 100 rows would convert to 4 columns of 25 rows... so on

So, the single column will be a multiple of 4, and thus, will be able to be converted into 4 columns of x number of rows.

If you have any more questions, let me know.

 

[Sky Tucker]

I should have been more specific in my last post so, I'm sorry for the additional questions. Will this be used more than once and, if so, will the data ever be in a different column (for example, it's in column 1 right now and it could be in column 2 next time)? Which column is the data in (this time)? Finally, if the count is not divisible by four, how would you like the remainder distributed?

 

[prose100]

I apologize for not being clearer. Hopefully this helps, it will be used more than once, but the columns will always be the same. Lets say, the single column is in column F, and the other 4 columns are in A thru D.

The number of rows may vary, but the count will always be divisible by 4.

 

[prose100]

Does anyone have any insights? They would be much appreciated. Thanks.

 

[Sky Tucker]

Hello Paul, sorry for the delay. Here is the code:

Sub Split_Columns()
Dim myDividend&, myTotal&, myRow&, myCol&
myTotal = Application.CountA(Columns(6))
myDividend = myTotal / 4
myCol = 1
For myRow = 1 To myTotal - myDividend + 1 Step myDividend
    Range(Cells(1, myCol), Cells(myDividend, myCol)).Value = _
        Range(Cells(myRow, 6), Cells(myRow + myDividend - 1, 6)).Value
    'Range(Cells(myRow, 6), Cells(myRow + myDividend - 1, 6)).ClearContents
    myCol = myCol + 1
Next
End Sub

I've assumed that the example columns that you provided are what you actually wanted. For this to work, the starting data must begin in row 1, column F. If you want it to clear out the original data as it goes, remove the apostrophe from the beginning of the 4th to last line.

Let me know if you have any questions or need any adjustments made.

-Sky

 

[prose100]

It works! Thanks Sky.