Stack columns but keep first 5

[teddzoon]

Hi,

I've been looking around to try and figure out how to most efficient stack columns.

My data (which is updated daily, hence have different amount of rows each day) currently have an output like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Name[/TD]
[TD]Code1[/TD]
[TD]Name[/TD]
[TD]Code2[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Starting Value[/TD]
[TD]Total Value[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]January[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XX[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]80[/TD]
[TD]85[/TD]
[TD]75[/TD]
[TD]40[/TD]
[TD]100[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XY[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]120[/TD]
[TD]160[/TD]
[/TR]
</tbody>[/TABLE]

I would like to stack the months, so my output would look something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Name[/TD]
[TD]Code1[/TD]
[TD]Name[/TD]
[TD]Code2[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Starting Value[/TD]
[TD]Total Value[/TD]
[TD]Month[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XX[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]October[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XX[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]November[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XX[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]December[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I tried recording a macro, but it did not handle different number of rows correctly.
So when I used the output on fewer rows, it still pasted values on fixed cells, instead of using "next empty cell available".

Have you got any magic tricks for me?

Kind Regards
Ted

 

[JackDanIce]

Welcome to the board. A pivot table can do this for you, removing need for macros.

Lots of online guides and tutorials on pivot tables for you to read up on.

 

[teddzoon]

Hi JackDanIce,

Then it might be something I'm missing completely. I've googled around quite a bit and not really found anything.
If I put the data in Pivot, and drag my columns to rows instead, I still dont get the desired outcome. The columns still put themselves in columns.

See this:
https://imgur.com/a/AQxORX1

It still does not stack the columns into a single coloumn.
What am I missing?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Name[/TD]
[TD]Code1[/TD]
[TD]Name[/TD]
[TD]Code2[/TD]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Starting Value[/TD]
[TD]Total Value[/TD]
[TD]Month[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XX[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]October[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XX[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]November[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XY[/TD]
[TD]99[/TD]
[TD]YY[/TD]
[TD]123[/TD]
[TD]Sales[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]March[/TD]
[TD]160[/TD]
[/TR]
</tbody>[/TABLE]



Cheers,
Ted