I would be grateful for help in doing the following:

I have list of names in col A followed by four items of numerical data in cols B to E. i.e.

A B C D E
Name1 7 8 4 7
Name2 4 5 1 9
etc.,

I want to turn this into

A B
Name1 7
Name1 8
Name1 4
Name1 7
Name2 4
Name2 5
etc

I have approx 500 different names. I will accept blank cells where names are repeated i.e. in the example A2 could be blank. Is there a quick way to do this please?

with grateful thanks for any ideas please.

Copy the range and ina new sheet using the Menu
Edit --> Paste Special option and choose Transpose and then OK
Voila. You are done

You can also use Transpose function.

Hi Neeraj

Thanks for your response. I have tried the Transpose feature but that just turns the whole range thru 90 degrees. This is not what i am trying to do. I need to transpose and realign the data vertically above each other as well as create additional space vertically for the name range which should remain in Col A, as in my example. I really need to find a way to reformat data in a report in the way I describe. I have tried for some time to do this but it's got me beat so far. Any ideas would be great.

with thanks regards

You would have to use VBA. Bill jelen includes a useful example, similar to what you desire (Chapter 5, pp. 108-9 in VBA and Macros for MS Excel). The book is worth buying for the many fine examples.