Two products on a Single date, Pivot date grouping through gaps

Rajendra_kodavaty

New Member
Joined
Oct 12, 2015
Messages
23
I have the following sample daily production data as below. The three digit code represent a product.
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">11-Mar-15   LBA
12-Mar-15   LBB
12-Mar-15   PXA
13-Mar-15   GNA
</code>
When i want to apply pivot i get the above data as below since two products are manufactured on same data
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">3/11/15 LBA
3/12/15 LBB
        PXA
3/13/15 GNA
</code>
I wanted to show the report based on month and I also understand that pivot will not be able to group the dates by month if there are any gaps in between. How can i bypass this to group dates by Month?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Canapone

Active Member
Joined
May 10, 2007
Messages
463
Hi,

what version of Excel is at your disposal? In Excel 2010 or more recent versions you can repeat item and fields labels.


Here some instructions:

https://support.office.com/en-us/article/Repeat-item-labels-in-a-PivotTable-report-882bdb55-9cdc-4d8d-b531-8e96e41dea31

 
Last edited:

Rajendra_kodavaty

New Member
Joined
Oct 12, 2015
Messages
23
My question was not to repeat the column labels but to group them as per the month. For example i have the following daily production report

Code:
3/11/15 ABC
3/12/15 GHI
3/16/15 DHI
7/20/15 YHU
7/20/15 FGI
7/21/15 FGI

When i convert it to pivot i get the same after checking repeat item labels but without checking the box you would find gap in last but one row under date.
I want the desired format to be displayed in months rather than dates as below

Code:
Mar-15 ABC
Mar-15 GHI
Mar-15 DHI
Jul-15 YHU
Jul-15 FGI

Now i have come to know that of there are any gaps the excel will not be able to group the dates in to months. How can i get over it to achieve the desired result?
 
Last edited:

Rajendra_kodavaty

New Member
Joined
Oct 12, 2015
Messages
23
Smitty, But i have posted the link of the post in other forum, which was allowed as per rules. How do you want suggest me to do otherwise
 

Watch MrExcel Video

Forum statistics

Threads
1,130,446
Messages
5,642,201
Members
417,259
Latest member
gtacw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top