Adding multiple rows in between existing tables

rrohan90

Hi Guys, my current table is something like this: 

And I want my output to be something like this...

Basically I wanted to keep year and month data consistent for all the categories, and add a dummy/blank row with category name so that I can do further calculations on the data e.g. moving average etc.

Please let me know how I can do it.

Cheers,

Rohan

RaviP

Hi @rrohan90, you could a crosstab and transpose and replace the null values with 0.

In the crosstab, have Month in 'Change Column Header' and Count in 'Values for New Columns'.

Then, in the transpose, you'll see all the months. Select Country, Cat and Year in 'Key Columns', and select the months in 'Data Columns'.

You can then replace the null values with 0

Hope this helps.

I just made a dummy data