# Manipulating data

#### mpd

##### New Member
I have a set of 2001 data points in 3 columns:

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

and I want to convert it to 9 columns:

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

Is there any way I can do this?

Big thanks to anyone who can help.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
HI

Do you want a formula solution, or a macro solution?

Tony

A formula solution would be great since I'm not that familiar with macros. But I'm open to whatever gets the job done best since I have 27 files to do this to.

Try the following. You should first select the top left cell in the range you want to adjust and then run the macro. This is potentially destructive, so be sure to have a backup copy of your data.

Sub ThreeToNine()
Do
ActiveCell.Offset(0, 3).Formula = ActiveCell.Offset(1, 0).Formula
ActiveCell.Offset(0, 4).Formula = ActiveCell.Offset(1, 1).Formula
ActiveCell.Offset(0, 5).Formula = ActiveCell.Offset(2, 2).Formula
ActiveCell.Offset(0, 6).Formula = ActiveCell.Offset(1, 2).Formula
ActiveCell.Offset(0, 7).Formula = ActiveCell.Offset(2, 0).Formula
ActiveCell.Offset(0, 8).Formula = ActiveCell.Offset(2, 1).Formula
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Delete
Selection.EntireRow.Delete
If Len(ActiveCell.Formula) = 0 Then Exit Do
Loop
End Sub

See
Managing data that include logical blocks
http://www.tushar-mehta.com/excel/tips/data_in_blocks/

The explanation might be a little too concise but the methodology is good.

I have a set of 2001 data points in 3 columns:

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

and I want to convert it to 9 columns:

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

Is there any way I can do this?

Big thanks to anyone who can help.

Hi

Assuming that your data is in columns A:C, starting in A1 then enter the formula

=OFFSET(\$A\$1,INT(COLUMN()/3)-2+(ROW()-1)*3,MOD(COLUMN(),3))

in F1, copy across to N1, then copy F1:N1 down as required.

Tony

also try;
Code:
``````Sub sample2()
Dim i, n As Long
Application.ScreenUpdating = False
n = 4
For i = 1 To Range("a" & Rows.Count).End(xlUp).Row
Cells(i, "d").Resize(, 3).Value = Cells(i + 1, "a").Resize(, 3).Value
Cells(i, "g").Resize(, 3).Value = Cells(i + 2, "a").Resize(, 3).Value
Cells(i + 1, "a").Resize(3, 3).Value = Cells(n, "a").Resize(3, 3).Value
n = n + 3
Next
Application.ScreenUpdating = True
End Sub``````

Replies
15
Views
669
Replies
4
Views
78
Replies
4
Views
260
Replies
7
Views
250
Replies
2
Views
109

1,214,320
Messages
6,118,883
Members
448,855
Latest member
joshpep96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back