Convert Excel Column Headers to Rows

I have an excel spreadsheet (Convert.xls) that has the due dates as column headers.  I need to create a row for each column header along with the data in column a of the spreadsheet (part number) and the value shown in the row for the header.  So in otherwords, if there are 10 column headers as dates, then I need to create 10 rows for the part number in column a.

See the spreadsheet (Desired.xls) attached for what the convert.xls should look like.

Thanks.
Convert.xlsx
desired.xlsx
maverick0728Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please follow the steps as shown in the demo video (link given below) to get the data in the desired format.

http://www.screencast.com/t/nP2Rja7nvrq
Convert.xlsx
0
 
ThomasMcA2Commented:
Excel has a built-in function to do that. It's called Transpose.

To use it, copy your source data (the headers), click in the first cell for the new rows, select Paste Special, check the Transpose checkbox, then click OK.
0
 
maverick0728Author Commented:
I've tried that and it works, but I need to repeat the part number for each row created with a column header.  See the spreadsheet "Desired.xls" for what is needed.  Thanks.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ThomasMcA2Commented:
It's the same process for multiple rows. Select H1 through BG2, copy, Paste Special with Transpose, then delete the zero rows.

Since it looks like you want more than just row 2 to get converted, you probably need a macro.
0
 
tomfarrarCommented:
If you have Excel 2010 or 2013, there is a tab add-in Power Query that can transpose the dates to rows for each part number.  So for instance if you have 10 columns with dates, and 10 rows now, the result would have 100 rows.  Is this what you are looking for?
0
 
tomfarrarCommented:
See attached.
Copy-of-EE-1.xlsx
0
 
tomfarrarConnect With a Mentor Commented:
Attached is your original spreadsheet with instructions and transformed data.
Copy-of-EE-1.xlsx
0
 
tomfarrarCommented:
Are you familiar with the Excel Power Query add-in?  I believe the answer is above, and Neeraj even provided a video on how to get the columns to rows.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.