2

So, I'm not very knowledgeable at all yet when it comes to VBA. I have experience with Java, so I understand the concepts of structure behind coding and can read/understand basic-intermediate code. But when it comes to writing it myself, I'm definitely still a 1.5 out of 10 in terms of writing VBA myself. So, any help would be greatly appreciated.

So I have the following example pivot table (my actual is about 10 years of data with a few more columns): enter image description here

The red and yellow dots are my problem areas. The columns with blank titles are just %differences from the previous row. However, as you can see, using that leaves blank spaces for the first month of every year (those are the yellow dots). Also, for the year lines, it doesn't calculate the %difference from the previous year (the red dots).

So, what I'm needing is (most likely) a PivotTableUpdate or PivotTableChangeSync (I still don't understand the difference actually by the way) to fill in data in those cells with the red and yellow dots. Any thoughts?

Edit: As requested, here's the data powering the pivot table:

enter image description here

For formatting purposes I moved the bottom half of the set up and to the right to fit it all into one picture.

dootcher
  • 3,059
  • 4
  • 22
  • 18
  • would you mind to provide some of your source data? lets say NP for 2011 till march 2012? Did you add a calculated Field? I don't know about the others, but I would like having some more informations about your configuration of source data and current pivot-setup. – Jook Sep 12 '12 at 08:22
  • Sure, no problem. Check the OP for my edit. It includes a picture of the data behind the pivot table. – dootcher Sep 12 '12 at 12:35
  • ok, i can nearly replicate your table, but when I add a calculated field, it does diyplay values in all parts, so this might be something with your formula - can you show how you calculate your %column? – Jook Sep 12 '12 at 12:59
  • I'm not actually using a formula for those columns. First, in the pivot table, make 2 of every column (i.e. 2 sums of NP, 2 sums of FUP, etc.). Then right click on your second version of every column and I'm using the "Show values as -> % difference from" feature. In the pop up window, change the base item to "(Previous)". Then do that for every duplicate column in the pivot table. – dootcher Sep 12 '12 at 13:27
  • ok, now i can replicate this! it really seems excel is not able to get it right, because it does not recognize december 2011 to be previous to january 2012 due to this grouping. – Jook Sep 12 '12 at 14:00
  • Yes, exactly. Now I just need VBA to fill in those blanks. I already wrote an equation and jerry-rigged a way to fill in the blanks using a really long and complex formula, but it involves having 10 pivot tables and vba to make them all sync up. However, that way is a bit slow at times (it sometimes takes like 10+ seconds to process), so I'm wanting to trying a different approach by seeing if using VBA to fill in the blanks (instead of what I'm using now) might be any faster. – dootcher Sep 12 '12 at 15:31
  • as of right now, I would say "leave the pivots - use subsum", but this should not matter - seriously, this question interests me. It is a common business problem - there should be an easy way to this. However, can't see the solution right now. But VBA shouldn't be necessary, although it might be. – Jook Sep 12 '12 at 15:40

1 Answers1

0

Without VBA you could try this attempt, i might extend it, but not today - it might be a way to solve this without VBA, but I am missing something.

enter image description here

E2=SUMIFS(D:D,C:C,C2)

F2=IFERROR(E2/E1;1)-1

On the PivotTable you add NP and Test2 to the section of values, then you can use Max or Sum for Test2 and it will look like this:

enter image description here

ATTENION here is an error, because summing the differences does not add up, when looking at the YEAR! But however, you can use the given formulas to get to the sums and differnces between the years ;)

Jook
  • 4,564
  • 3
  • 26
  • 53