I have a potentially difficult scenario to try and get help with.

I have a list of shift start and end times e.g. 7am - 5pm as well as the number of people working each of these day in a table.

It has days of the week across the top and then each shift pattern listed down the side. I then have a list of 'demand' sorted by hour of the day for each day of the week. I want to list down in columns starting at 00:00 Monday until 23:00 Sunday corresponding numbers of people and demand so that I can match them up. Somehow the shifts need to be converted into a list of hours and matched against the numbers in the table and then the demand needs a similar process!!

I don't even know where to start on this one so any help is greatly appreciated.

Could you post an example worksheet?
It will make it far easier to help you out.

I have tried to upload it but it didn't work so I have put in a picture of the shift pattern sheet. I also have another sheet that has like:

00:00 - 100
01:00 - 130
02:00 - 124

etc for each hour.

Try adding the file to a .zip (or compressed file) and attaching that.
From the .jpg you've attached it's still not clear what you're trying to achieve.
(well not to me at least!)