Pivot table update within groups

mlmoyer

New Member
Joined
Sep 25, 2008
Messages
15
I have a data table that looks somewhat like this a number of rows of "Date", "ID#" and other extraneous fields.

I have a pivot table that simply lists the total count by date, then i grouped it by month and then by quarter:

<table x:str="" style="border-collapse: collapse; width: 220pt;" border="0" cellpadding="0" cellspacing="0" width="293"><col style="width: 69pt;" width="92"> <col style="width: 43pt;" span="2" width="57"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt; width: 69pt;" width="92" height="18">
</td> <td style="width: 43pt;" width="57">
</td> <td style="width: 43pt;" width="57">
</td> <td style="width: 65pt;" width="87">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl22" style="height: 15.75pt;" height="21">Date4</td> <td class="xl23">Date2</td> <td class="xl23">Date</td> <td class="xl26">Count of ID</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">2008-Q4</td> <td class="xl28"> </td> <td class="xl28"> </td> <td class="xl24" x:num="" align="right">76</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl33" colspan="2" style="">October 2008</td> <td class="xl32" x:num="" align="right">29</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39722">10/1/08</td> <td class="xl25" x:num="" align="right">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39736">10/15/08</td> <td class="xl25" x:num="" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39742">10/21/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39752">10/31/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl31">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl33" colspan="2" style="">November 2008</td> <td class="xl32" x:num="" align="right">25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39753">11/1/08</td> <td class="xl25" x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39767">11/15/08</td> <td class="xl25" x:num="" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39773">11/21/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39781">11/29/08</td> <td class="xl25" x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39782">11/30/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl31">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl33" colspan="2" style="">December 2008</td> <td class="xl32" x:num="" align="right">22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39783">12/1/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39797">12/15/08</td> <td class="xl25" x:num="" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39803">12/21/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39813">12/31/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl31">
</td> <td>
</td> </tr> </tbody></table>

My problem is, when i add a new row to the main table, say with a date of "December 20, 2008", and refresh the pivot table, the information ends up clear at the bottom (in this case, after the year 2014).

What am I doing wrong? I figured the pivot refresh would place the data among the other December 2008 rows in the pivot table.

Thanks.

Mark :confused:
 

nigelk

Well-known Member
Joined
Aug 30, 2008
Messages
535
Try clicking on the range and select "field settings" then "advanced" you can then sort ascending/
descending.
 
Last edited:

mlmoyer

New Member
Joined
Sep 25, 2008
Messages
15
thanks - I tried your suggestion but it didn't move; new entries still end up at the very bottom. Does grouping have anything to do with it?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Your date may be interpreted as text - if you enter it as 12/20/2008 does it behave properly?
 

mlmoyer

New Member
Joined
Sep 25, 2008
Messages
15
I checked both the format of the maindata and the pivot table fields, and they're both date fields. For grins i tried resorting (thru Field Settings/Advanced) and it didn't move; refreshed; resorted; still at the bottom.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
if you change the format of the cell where you just entered the date, does the display change? If not , it's text and not a date.
 

mlmoyer

New Member
Joined
Sep 25, 2008
Messages
15
<table x:str="" style="border-collapse: collapse; width: 250pt;" border="0" cellpadding="0" cellspacing="0" width="333"><col style="width: 69pt;" width="92"> <col style="width: 73pt;" width="97"> <col style="width: 43pt;" width="57"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl35" style="height: 12.75pt; width: 69pt;" x:num="39724" width="92" height="17">10/3/2008</td> <td class="xl36" style="width: 73pt;" width="97"> </td> <td class="xl26" style="width: 43pt;" width="57"> </td> <td class="xl24" style="width: 65pt;" x:num="" align="right" width="87">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17"> </td> <td class="xl37" x:num="39724">October 2008</td> <td class="xl34"> </td> <td class="xl33" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17"> </td> <td class="xl38">
</td> <td class="xl28" x:num="39724">10/3/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl27" style="height: 13.5pt;" height="18"> </td> <td class="xl38">
</td> <td class="xl29">
</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl31" style="height: 16.5pt;" height="22">Grand Total</td> <td class="xl39"> </td> <td class="xl30"> </td> <td class="xl32" x:num="">2014</td> </tr> </tbody></table>
All three pivot table columns are dates (i checked Format Cells). I changed the format of the cells in the physical data itself to "General" and all the dates changed to the typical number it should be, then i set them back to "Date" and they're back to mm/dd/yyyy.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Ah - how did you do the grouping? Did you just right-click the date field, choose Group and show detail->group, and then choose year, quarter and month?
 

mlmoyer

New Member
Joined
Sep 25, 2008
Messages
15
I selected weeks to be a selection, right-clicked and "grouped" them into a month, then selected months and grouped them into quarters. ... i just selected the field - it's just a standard date field - or am I misunderstanding your question?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you grouped them all manually like that then you won't get automatic grouping. If you drag your date field to the row area, then right-click it and choose Group and show detail->Group, you should get options to group on Weeks, months, quarters and years. If you use that method, it should automatically group any additional data.
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top