Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Since the Pivot Table won't accept the formula in the calculated field, how can I get the formula in the adjacent column to update the RANK's reference range using a macro or some other function? Can OFFSET help? I'm not really clear on how it might (if at all) be used.


    Actually, when I first wrote this I had the question at the end. But I don't want to waste anyone's time, so feel free to close this thread at any time. I can't see you so my feelings won't get hurt. And now for the explanation:

    I have a report (exported to Excel) of monthly results of over 10k customers to which I have added a field classifying the customers by level or type of activity into types 0, B, S, T and Z. In addition to regular business activity, customers earn or redeem points, all captured in this one monthly spreadsheet. Type Z customers have earned or redeemed points but did not transact typical business activity. Customers may sometimes be awarded points earned as an adjustment or even on a discretionary basis. I would like to identify the customers who have earned or redeemed points but did not transact typical business activity, resulting in an Exception Report.

    I have created a pivot table from the data to filter one group, Type Z, for Points Only customers. For the Pivot Table Report Filter I select Type Z and bring Customer and Customer ID into the pivot table Rows. Sum of Points Earned and Sum of Points Redeemed are Values in the next two columns (columns C & D). In column E (not part of the Pivot Table), labeled "Rank", I have the formula: =IF(OR(RANK(C16,C$16:C$403,0)<=20,RANK(D16,D$16$403,0)<=20),1,0).

    If a customer's Earned or Redeemed points Rank in the Top 20 the formula will post a 1 in the cell, otherwise zero. From there I can do a Data Filter on the column to show only the customers with a 1.

    I tried to Insert Calculated Field into the Pivot Table, but get the error "References, names and arrays are not supported in Pivot Table formulas".

    The numbers of customers will change from one month to the next. That leaves me with manually revising the RANK formula range each month. I'm trying to automate some/most of the process by developing small chunks of VBA code.

    So, by now I'm sure you're asking "what's the question"?

    (This is where I originally had the question, which I have since moved to the beginning tf this thread.)

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Can you provide a data sample with non-proprietary data along with a sample of what the report should look like?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    The sample file is attached. It's saved as a macro-enabled file but there are none in this version. This file is about one-tenth the original size.

    Thanks for checking this out.

    Just noticed that I uploaded the file with the RANKing Data Filter (col E) set to ALL. You should change the value to 1 before printing or viewing.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Arcturus16a View Post
    ........
    I would like to identify the customers who have earned or redeemed points but did not transact typical business activity, resulting in an Exception Report.
    ........

    If a customer's Earned or Redeemed points Rank in the Top 20
    .........
    I made a guess at what might be needed. Attached are two Pivot Tables. Each table shows only top 20 for CustType of "Z", tables are sorted in order of highest to lowest. The tables are using Net Points (Points Earned minus Points Redeemed). One uses straight calculated values, the other uses the "absolute" numbers.

    Is this headed in the right direction?

    I had to Zip the file to stay within the Lounge's file size limitations.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    In the raw data there are some things that may create problems when doing any analyses.
    There are many CustName with same name but different CustID. In addition, there are many CustID with same ID but different CustName.

    see pic below ....
    [attachment=90574:Strange data.jpg]

    Note that CustName of MANSO has 3 different CustID(s).
    Also, CustID 1032 has 13 different Cust Name(s).

    Please clarify reasoning.
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •