Formatting Imported Data

[Fabs]

I have to compile a worksheet from data copied from a pdf file. Problem when
i copy and paste text all data falls into column A and i have to drag one
line at a time to the specific headings.

this is how the data I copy pastes into excel:

Date
Time
Duration
Number Dialled
Charge
01/05/2009
11:14:03
00:00:30
110
0.79
07/05/2009
19:20:59
00:01:41
0860112442
0.00
28/05/2009
18:40:20
00:01:45
110
1.25
28/05/2009
18:48:51
00:00:07
0860123911
0.15
Sub Total
2.19

I have set up the columns as such:
Date Time Duration Number Dialled Charge

01/05/2009
11:14:03
00:00:30
110
0.79


And need to move the relevant information to the relevant columns without
having to drag one item at a time. I have 25 pages of information to work on
with approximately 1800 entries!
Any suggestions would be greatly appreciated,
Fabs.

 

[Stefi]

If your pasted data are in column A starting from row 1 then insert this
formula in B1 and fill it to the right until column F and down as required!

=INDIRECT("A"&(ROW()-1)*5+COLUMN()-1)

Regards,
Stefi

„Fabs†ezt írta:

 

[Don Guillett]

Sub transposedata()
Dim mc As Double
Dim r As Integer
Dim i As Long
mc = 1 'Column A
r = 1
For i = 1 To Cells(Rows.Count, mc) _
.End(xlUp).Row Step 5
Cells(i, mc).Resize(5).Copy
Cells(r, mc + 1).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
r = r + 1
Next i
End Sub