Transposing and consolidating a 1d to a 2d table

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

You can use Advanced Filter to get a unique list of names from your data into another sheet. Insert a new row 1 and put headings in row 1 (like Name, Criteria, Value) and embolden them. Then copy column A into column A of a new sheet, then highlight all the data including the header and click on Data | Advanced (Filter) and in the dialogue box click against Copy to another location, in the Copy To box enter C1, and click Unique records only then OK. Then you can delete columns A and B, leaving you with the unique list of names in the new column A. Put the other headings in B1:D1, i.e. Color, Gender, Age. Then you can use a VLOOKUP formula or an INDEX/MATCH combination to retrieve the appropriate data.

Hope this helps.

Pete