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.

Unique ID	Text 1	Text 2	Apr	May	Jun
567926	ABC	zafads	5	5	5
704740	DE	fadf	5	5	5
704765	DE	aafds	5	5	5
506511	DE	adfa	5	5	5
705258	DE	adfa	5	5	5
705259	DE	afd	5	5	5
705260	DE	afd	5	5	5
705261	DE	afd	5	5	5
705262	DE	afd	5	5	5
705263	DE	afd	5	5	5

<tbody>
</tbody>

I need to convert it to this:

567926	ABC	zafads	Apr	5
567927	ABC	zafads	May	5
567928	ABC	zafads	June	5
567929	ABC	zafads	July	5
567930	ABC	zafads	Aug	5
567931	ABC	zafads	Sept	5

<tbody>
</tbody>

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.