Move Date from Grid Format to List Format

Hi

I have data that is in a grid format, with locations in rows and weeks in columns.

The locations are in column A and the weeks are sequentially in columns B:CA with data in each of the intersections.

Is there a way in which I can transform the data so that each column appears under the one before i.e. column A repeats with locations, that the header is taken from B2 and repeated for the length of A and then the data inserted in the next column

Probably far best illustrated with an example. See attached.

Cheers

Hi

Thanks for your help.

It looks as though it is the right answer BUT but I cant get it to work in Excel 2007.

Anyone more ideas?

MCC