Excel Transpose Question?

[sgpack]

[FONT=&quot]I have a set of data with Departments, Names, Dates of Expense, and Expense Amount. [/FONT]

[FONT=&quot]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: [/FONT]

[FONT=&quot]Department // Name // Jan // Feb // Mar [/FONT]
[FONT=&quot]Accounting // Bob // 5 // 0 // 15 [/FONT]
[FONT=&quot]Finance // Joe // 6 // 7 // 0 [/FONT]

[FONT=&quot]Instead I need the data presented with the date and amount vertically: [/FONT]

[FONT=&quot]Department // Name // Date // Amount [/FONT]
[FONT=&quot]Accounting // Bob // Jan // 5 [/FONT]
[FONT=&quot]Accounting // Bob // Feb // 0 [/FONT]
[FONT=&quot]Accounting // Bob // Mar // 10 [/FONT]
[FONT=&quot]Finance // Joe // Jan // 6 [/FONT]
[FONT=&quot]Finance // Joe // Feb // 7 [/FONT]
[FONT=&quot]Finance // Joe // Mar // 0 [/FONT]


[FONT=&quot]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. [/FONT]

[FONT=&quot]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??[/FONT]

 

[=ODIN=]

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).