Transpose or array?

[mpearce]

I have a file that contains patient data and is in the form of:

____A______ _|_____B______|____I____|
1 | Name ..........|.................. |............. |
2 | Account# ...| admitDate ....|............. |
3 | MedRecord# |................... | Balance ..|
4 | Name2........ |................... |............. |
5 | Account#2 ..| admintDate2...|............. |
6 | MedRecord#2| ...................| Balance2 |


This goes on for 6936 rows. What i need is to covert this format into this

Patient Name_|AccountNumber|AdmissionDate|Balance
1| Name ...... | Account# ......| AdmitDate ...| Balance
2| Name2 .....| Account#2 .....| AdmitDate2 ..| Balance2

Can someone assist me in doing this?

Thanks

[GTO]

Please attach a workbook layed out just like yours, excepting of course, with fake names and account numbers.

Mark

[mpearce]

here is a sample of the data that I am working with.

[JKwan]

Try this:
http://www.cpearson.com/excel/ColumnToTable.aspx

[mdmackillop]

Is your data fully consistent, ie 3 rows per patient with the relevant data in the same relative positions for each?

[GTO]

Hi again,

Not a complaint, but just to make sure. You have shown a total of two records and the balance is in Col K, rather than in Col I as in post #1.

Is the data actually layed out this consistently? That is, every record has the same number of rows, there are no skipped rows, etc.

If so, can we count on the data (the first name) starting at a certain row?

Mark

[mdmackillop]

As JKwan suggested