• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding difference on pivotted matrix

ranjit1964

New Member
i AM TRYING TO FIND TO FIND DIFFERENCE OF DATA VALUES IN THIS PIVOTTED WHERE ROW HEADERS AND COLUMN HEADERS ARE SAME.

HOPE I HAVE EXPLAINED WELL.

REGARDS
RN
 

Attachments

Peter Bartholomew

Well-Known Member
I do not think this is in any way a natural pivot table operation.

There appear to be two points at which you can attack the problem. The first is to duplicate the data and then reverse the Co and RC columns for the copy while changing the sign of the balance. The new dataset is then appended to the original. On refresh, the pivot table will show an antisymmetric table of values representing the differences.

The second approach is to form the existing PT and then extract the data using GETPIVOTDATA with array parameters rather than the default hard-wired values. This again allows the array to be subtracted from its transpose to give a table of differences.

(Edited to substitute CSE arrays for dynamic arrays)
 

Attachments

ranjit1964

New Member
I do not think this is in any way a natural pivot table operation.

There appear to be two points at which you can attack the problem. The first is to duplicate the data and then reverse the Co and RC columns for the copy while changing the sign of the balance. The new dataset is then appended to the original. On refresh, the pivot table will show an antisymmetric table of values representing the differences.

The second approach is to form the existing PT and then extract the data using GETPIVOTDATA with array parameters rather than the default hard-wired values. This again allows the array to be subtracted from its transpose to give a table of differences.

(Edited to substitute CSE arrays for dynamic arrays)
Thank you so much Peter.
The explanation on being this not being a natural pivot opertation has answered my doubt.
On the other two ways, will try same.

what i have done was i paste special pivot data on seperate space and built a vlookup based with Row & Column. GETPIVOTDATA may assist avoiding a pastespecial.

Regards
Ranjit
 
Top