Can excel do this:

[JOhnH]

I have a text file that is one collum and about 16,000 rows. I have no control over the creation of the source file.

The rows are sequenced such that 8 rows make a record and repeat. Every other row/cell is blank.
Like this:

A1 [empty]
A2 Date
A3 [empty]
A4 Last Name
A5 [empty]
A6 First Name
A7 [empty]
A8 Comment
A9 [empty]
A10 Date
A11 [empty]
A12 Last Name
A13 [empty]
A14 First Name
A15 [empty]
A16 Comment

Can I reformat this such that each sequence of 8 Column cells is moved into 8 sequential Row cells?

A1 thru A8 would get moved to C1 thru J1
A9 thru A16 would get moved to C2 thru J2
etc
etc

 

[Alexb2000]

Seems like something you could do with a simple data migration tool, like:

http://www.zoho.com/creator/help/application/ms-access-and-excel-migration-tool.html

 

[CT4ME]

Using the "transpose" feature of Paste Special, you can turn the columnar data into rows. A macro or formula could automate the procedure.

 

[JOhnH]

Seems like something you could do with a simple data migration tool, like:

http://www.zoho.com/creator/help/application/ms-access-and-excel-migration-tool.html

Thanks, I will look into that. The 15 day free trial should allow me to transpose the first 8 years worth of data, then the ongoing free version should be enough to allow me to keep up with the input on a regular basis. But for this one project, i can't see paying $25/month.

 

[JOhnH]

Using the "transpose" feature of Paste Special, you can turn the columnar data into rows. A macro or formula could automate the procedure.

That worked great for the first 8 fields.
If there is a way to get it to repeat 2,000 more times that would be just what I need.

 

[Van Johnston]

That worked great for the first 8 fields.
If there is a way to get it to repeat 2,000 more times that would be just what I need.

Figure out how to record a macro. If it was me I would cut-paste transpose-delete the 16 now blank rows-highlight new first cell of data -end macro. Then you have to hit your macro key 2000 times. Less than 35 minutes at 1/sec.

I'm sure there are more elegant ways but that is the way I would brute force it.

 

[EdFred]

When I get home tonight I can write some code to take care of this if you want to send me the text file

 

[CT4ME]

Here's a start...
Assign this macro to a key... each time you push it, it'll do the transpose thing on 8 more rows...
Sub convert()
'
' convert Macro
'

'
ActiveCell.Offset(8, 0).Range("A1:A8").Select
Selection.Copy
ActiveCell.Offset(0, 5).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(0, -5).Range("A1").Select
End Sub

 

[LDJones]

A simple conversion program could be written in five or ten minutes by any programmer. I used to crank those out all the time...but now would probably take me an hour since I am woefully out of practice!