Converting data - how to...

I have an Excel file that is a repetitive list of data. Instead of having a column with rows of data underneath we have Column A listing over and over what should be the column headings and then the corresponding data is in Column C.



For Example:

Name John Doe

Address 1234 street

***

Name Jane Doe

Address 1235 Street

***



What I need to do is Make a table in Access that looks like this:

Name Address

John Doe 1234 Street

Jane Doe 1235 Street



I tried using a CrossTab query and a Pivot table but they only will return either a count of my records or just the first or last row, etc. How can I convert this table and return all rows?



Any help would be appreciated!



Thank you!!

Jeanna

You can't easily. If the "***" is really in your worksheet than at least you know where the new "record" starts. the rest is parsing the entire sheet and transfer the values one-by-one with a VBA script.



I often have situations of this kind. Normally, it is a customer who sends us his "database". In general, I send back the file stating that this is no good and require a properly formatted sheet that can be imported with the wizard. That goes much faster (and cheaper in the end) then struggling with data that people assembled like sheets of paper in a trash bin.

Actually I worked with a really good Access programmer on this issue and you can check out the code, etc at the link below. It worked great!!



URL