Transpose Column Data to Rows?

[kestefon]

Sadly, this isn't a simple Paste Special > Transpose problem.

I have a table of data with certain values presented as columns. i.e. Price in one column, units in another column, etc. I need to transpose the data so that there is only one column for values, and another column that labels what the value is. I know how to do this in Pivot Tables, but Excel crashes when my dataset becomes too large (i.e. hundreds of thousands of rows).

My attachment should clarify what I'm trying to do.

TransposeExample.xlsx

[AB33]

Try the attached.

[Trebor76]

Excel maybe crashing when you try and pivot the data as you may be running out of rows as you say there are hundreds of thousands of rows which, when transposed are 5 rows a piece. This will exceed Excel's 2007 and later row limit of 1,048,576 so a macro doing the same could cause the same issue (and probably take ages to run).

[AB33]

If you delete the first row with word "Original" and the headings which go in to column 4 are in row1, see adjusted code.

[kestefon]

Thanks!

Quick question...is VBA the most effective way to do this, or is there some normal Excel trick I'm not taking advantage of? I'm pretty sure it could be done with VLookups or Index/Matching, but that seems like it could be cumbersome. Would the transpose function have any application here?