restructuring data

I have a spreadsheet with data points collected on different lines/tracks. Line/track names are Line 1, Line 2 ...etc. The spreadsheet is ordered by these lines/tracks with the line/track name as heading and the points with the information on rows. Each time there is a new line/track there is a blank row and then a heading with the new line/track name and the points data following. To import the data into a database I must get rid of the line name heading and put the line name in a new column, i.e. each record will have a field/column called "Line_name". getting rid of the header rows is not such an issue, but getting the line names in a new column will take a long time.

Is there a way to reorder this without copy and pasting hundreds of lines?


see attached spread sheet.

With code, yes, but you really should lose those merged cells. They make life really difficult.

EDIT: de-merge the Line Number cells and try this:

Please Login or Register  to view this content.

Thanks for the quick reply.

the code put me on the right track, but I think something is amiss with an offset somewhere. It did not like the empty rows in the spreadsheet, so I removed all the unnecessary rows. This causes an offset problem though; the first 2 records after the first header is missing in the results and the 1st 2 records of subsequent headers gets the previous line number assigned to it.

I attached the result if you can please assist again, my VB is not so good.
Actually, I think I stuffed it up good now, the 1st line number continues far down the list...

thanks
Wilhelm