Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004

    Unanswered: Transpose horizontal data to vertical

    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

    I want to turn this into

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

    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.

  2. #2
    Join Date
    May 2005
    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.

  3. #3
    Join Date
    Oct 2004
    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

  4. #4
    Join Date
    Oct 2003
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts