Manipulating data

[mpd]

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.

 

[acw]

HI

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


Tony

 

[mpd]

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.

 

[Artemus]

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

 

[tusharm]

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.

 

[acw]

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

 

[agihcam]

also try;

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