Get data from split/merged cells

Hi All,

Before joining and posting my question I’ve used the search function and not found and answer to my problem. Have found plenty of messages from people to others telling them to steer well clear of merging cells because it just causes grief but unfortunately this workbook has been given to me and I’m trying to convert the information so I can import it into an SQL database.

I’ve attached a small version of the workbook because my explanation confuses myself let alone others.

http://www.garyrowe.co.uk/stuff/xls/test.xls

On the SOURCE sheet is the table I’m struggling to work from. It has information on Bridges that I’m trying to merge into one cell with HTML code wrapped around it. This is simple as I’ve created the code on the OUTPUT sheet.

However due to the fact that there are split/merged cells describing which Lines and Protecting Signals surround a bridge when I auto fill down on the OUTPUT sheet I get a lot of data missing from the initial row and then rest appearing in the cells below but with some information missing.

My ideal OUTPUT would have all the information for one bridge per row wrapped in HTML.

You’ll get the idea if you see the workbook. I can’t figure out how to achieve the result I need in any form of formulae and don’t know the first thing about VB.

Please help!
Thanks.