• 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.

Pivot Table, Calculated Field

C

cubs610

Member
Hi Everyone,

This post may be a little premature, but figured I'd ask. One of the people I work with deals with Pivot Tables and need some help. Below is the email he sent; of which I cannot offer a solution since it, from my perspective, does not offer any real substance. Thought maybe some of you might be able to pick up something I did not. See direct quote below in italics.

...I have been given a gigantic PivotTable with a lot of numbers showing data of credit card declinations according to different reasons. I am only interested on getting the most important data from that PivotTable to see where I am loosing more money. Instead of filtering the table until I get to my results as I do now, I need to find a faster way to get just to the important data and figure my opportunities, do you have some ideas that can help me develop this project?...

FYI- I sent a note to him ask for a lot more information, so odd are I'll add some supplemental info to this post. In the meantime-- many thanks!

-Dave
 
Hello Dave

If your colleague's definition of *important* is the declination reason with the greatest total transaction value all they need to do is have declination reason as a row label, sum of declined transaction amount as the value then simply sort on value high to low.

How much faster do they need a 20 second process (after clean the data) to be?

Same would apply to count of reasons, if the most frequently occurring is more important than the total value, although they may be linked.

Regards,
Alexis
 
Hi Alexis,

Thanks, I appreciate it. I'll definitely pass it along. Subsequently I asked him to clarify his original email/ send more info. Below is his response.

What I basically have is a PivotTable where I have as my coulmn headings names such as bank name, type of product, reason why my card was declined, date and a couple more headings. From all my data (aprox. 3000 rows) I'm looking to select the most relevant reasons (out of the a hundred I have) and create a new pattern with just the most relevant reasons, so in the future I wont have to look through my entire table.


Thanks again!
-Dave
 
Hi cubs610,

Add two helper columns, one should count for how many times each reason is repeated, second is another formula, can you see the attached file...
 

Attachments

  • Analyse Reason.xlsx
    12.7 KB · Views: 3
You must log in or register to reply here.
Back
Top