Excel Table With Columns Months to Rows With Months

[Tardis]

Hi,

I've got a table with a unique ID and some text columns and some numbers for each month.

[TABLE="width: 410"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Text 1[/TD]
[TD]Text 2[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]567926[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]704740[/TD]
[TD]DE[/TD]
[TD]fadf[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]704765[/TD]
[TD]DE[/TD]
[TD]aafds[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]506511[/TD]
[TD]DE[/TD]
[TD]adfa[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]705258[/TD]
[TD]DE[/TD]
[TD]adfa[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]705259[/TD]
[TD]DE[/TD]
[TD]afd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]705260[/TD]
[TD]DE[/TD]
[TD]afd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]705261[/TD]
[TD]DE[/TD]
[TD]afd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]705262[/TD]
[TD]DE[/TD]
[TD]afd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]705263[/TD]
[TD]DE[/TD]
[TD]afd[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

I need to convert it to this:

[TABLE="width: 346"]
<tbody>[TR]
[TD]567926[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD]Apr[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]567927[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD]May[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]567928[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD]June[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]567929[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD]July[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]567930[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD]Aug[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]567931[/TD]
[TD]ABC[/TD]
[TD]zafads[/TD]
[TD]Sept[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Where the Unique ID and Text is duplicated and the months are broken out with their numbers.

Using Paste Transpose for each row is not feasible as I have thousands of rows.

Help? How?

Thanks,
Byron

 

[Misca]

The unpivot feature in Power Query was created for this kind of tasks. Power Query is a free add-in for Excel 2010 that became the Get & Transform found in the Data tab in later versions of Excel. Just google for "Power Query for Excel 2010" to download & install the app.

Once you have installed Power Query to your computer follow this tutorial and you'll be done in seconds.

 

[Peter_SSs]

Can you show us a bit more of the results so we can see a pattern and where the subsequent results should go?

 

[Tardis]

Thanks I'll check it out.

 

[Tardis]

The result is just 12 rows for each month with the information. However, I've written VBA to do the insert, copy, and transpose to get the result I want. I was hoping that there was a way using pivot table, or some other built in feature of Excel.