What Excel formula can I use to transpose rows into columns, but still retain the header data?

Question

Part of my job is to track lists of items that will need to go through multiple phases, and we budget out the number of days for completion of each phase. I will need to upload this information to another system, and in order to do so it needs to be formatted in a certain way.

I need to take the data from the table and transpose the rows into a consolidated column, but each row still needs to be labeled with the proper phase number.

I've created the following example using only 3 item lines, but I can often have anywhere up to 50+.

Basically, I need to take data like this:

And somehow get it to look like this, in as few steps as possible:

(note how my example skips the blanks and only retains cells with actual information; That would be EXTREMELY preferable, as it'd be a pain in the butt to take those rows out manually every time)

I've experimented with VBA/Macros to do this too (written by GPTExcel, not by me, I have no idea how they work) but since it's just AI trying to guess what I want, it never turns out quite right. I'm of course willing to go that route if a formula wouldn't do it, but honestly I just don't know enough about it to determine that.

Answer 1

Hi,

You can use Power Query to get the result:

1. Select the Range- go to Data- From Table/Range- open Power Query Editor:
   
2. Select Column PHASE1 to Column PHASE8- go to Transform- select Unpivot Columns:
   
3. Filter the Value column: uncheck "-"- Close & Load:
   

Hope it's helpful.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.