Solved

Convert Excel Column Headers to Rows

Posted on 2016-07-14
8
96 Views
Last Modified: 2016-07-18
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
0
Comment
Question by:maverick0728
8 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 41711311
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
 

Author Comment

by:maverick0728
ID: 41711346
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
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 41711392
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Expert Comment

by:tomfarrar
ID: 41711397
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
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41711402
See attached.
Copy-of-EE-1.xlsx
0
 
LVL 7

Assisted Solution

by:tomfarrar
tomfarrar earned 250 total points
ID: 41711453
Attached is your original spreadsheet with instructions and transformed data.
Copy-of-EE-1.xlsx
0
 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41711703
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
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41713618
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question