I am using Excel 2010. I am trying to convert a single dimension table similar to this:

before.png

I need to consolidate and transpose the data into a two dimensional table that looks like this:

after.png

I've tried using the "transpose" option of the Paste Special. I receive an error because of the fact that I have 135,000+ rows and Excel 2010 only supports 1,048,576 rows by 16,384 columns.

Transpose.xlsx