transpose rows to columns

I have a spread sheet with column A header row is "GLAccountNumber","Description","Date","Balance" and the data for row 1 would be "1000","Cash","Jan-13","100" then row 2 would be "1000","Cash","Feb-13","200" and so on till Dec-13 then row 14 is the next account number starting at Jan-13 again. This goes on for approx 9500 rows. What i want it transposed to is header row would be "GLAccountNumber","Description","Jan-13","Feb-13" etc thru "Dec-13" and the balances for each month under it so I have one row per account number. What would be the easiest way to accomplish this? Please help

Select range("C2:D" & LastRow) and then select cell E2 and right-click >> Paste Special >> Check Transpose ..
That's all I think

Originally Posted by YasserKhalil

Select range("C2:D" & LastRow) and then select cell E2 and right-click >> Paste Special >> Check Transpose ..
That's all I think

That makes two rows where row 1 is "Jan-13" thru "Dec-13" then "Jan-13" thru "Dec-13" and so on for about 9500 columns. Not exactly what I wanted. I have attached a sample of what I have vs what I want using only 4 different accounts. I could use the method you described for each account but would have to copy and paste the row it makes for each account for approx 796 different accounts.

I played around with the pivot table feature and was able to use it to transpose the data. Problem solved. Thanks

Try this code

Please Login or Register  to view this content.