Excel Transpose Question?
Results 1 to 2 of 2

Thread: Excel Transpose Question?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member (M)
    Join Date
    Oct 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Transpose Question?

    I have a set of data with Departments, Names, Dates of Expense, and Expense Amount.

    Currently the data is set with the Department and Name in Columns A and B // Jan - Dec as the heading // Expense Amounts in each cross section:

    Department // Name // Jan // Feb // Mar
    Accounting // Bob // 5 // 0 // 15
    Finance // Joe // 6 // 7 // 0

    Instead I need the data presented with the date and amount vertically:

    Department // Name // Date // Amount
    Accounting // Bob // Jan // 5
    Accounting // Bob // Feb // 0
    Accounting // Bob // Mar // 10
    Finance // Joe // Jan // 6
    Finance // Joe // Feb // 7
    Finance // Joe // Mar // 0


    Currently, I paste special with transpose, and use a fill blanks macro to fill in the "Accounting" and "Bob" sections. The only problem is I have over 4000 unique combinations of Departments/Names, and my time frame is 10 years, not three months. If I do the simple paste special - transpose, I have to manually cut move the next department/name to the end of the list.

    Is there a way to automatically do this for me? I've tried implimenting Pivot Tables, but the column headings become Jan, Feb, ect. instead of "Date", I've also tried creating a macro but am terrible at VB. Can anyone help here??

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Transpose Question?

    The pivot table should get you mostly there -- once it's set, there should be something at the bottom right of the "Pivot Table Filed List" window that says "Values". It will be in the upper right quadrant under "column labels". Move it to "Row Labels" (the bottom left quadrant).

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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