Data is in one column, need it spread across three!!!

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Hi All,

I have been given a load of data that is vertical i.e. as follows,

Fred
22
3
Bill
55
6
etc

But it needs to be

Fred,22,3
Bill,55,6
etc

I can't see how to do this, can anyone help please?

Regards.
Bill.
 
Enter this formula in B1.

=INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:B)-1)

Copy across to D1

Select B1:D1 and copy down until you get zeros.

Or use this macro..........

Sub ColtoRows()
Dim rng As Range
Dim I As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = 3 '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
Exit Sub
End Sub


Gord Dibben MS Excel MVP
 
Let's assume "Fred" is in cell A1 of Sheet1
On Sheet2 in A1 enter =INDIRECT("Sheet1!A"&ROW()*3-2)
On Sheet2 in B1 enter =INDIRECT("Sheet1!A"&ROW()*3-1)
On Sheet2 in C1 enter =INDIRECT("Sheet1!A"&ROW()*3)
Copy the three cells down the rows to capture all your data

Need to change these formulas to values? Select all the cells; use COPY,
with the cells still selected use EDIT | PASTE SPECIAL and specify Values
best wishes
 
Back
Top