Sum excel data when one value is present in a pairing but ignore / not sum when both are present

Kevin Buckland
Kevin Buckland used Ask the Experts™
on
EE-Codes.xlsxI have a fairly large dataset ~750K rows.  

The relevant columns are:
Col B (Member#)
Col E (Proc Code)
Col G (Paid Date)
Col H (Begin Date)
Col I (End Date)
Col K (Paid $)
Col M (Provider#).  

I have it in setup in pivot table to display by Provider# and Member# and then it lists the Proc Codes, Begin Date, End Date, Paid Date and Paid $.  I am having trouble figuring out how to isolate the data I want.  

For each unique pairing of Member # (Col B) and Provider # (Col M); I need to identify Proc codes (column E) whereby Proc code D2950 is present BUT for the same Member# | Provider# pairing there's no value of D2740. In other words, I'm trying to detail and sum the Paid $ for all pairings where only D2950 is present but ignore the pairing when D2740 is also present.  

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
A sample file would make it much easier to understand your requirement.

Author

Commented:
Thank you, I uploaded an excerpt of data.  

In the pivot table for each pairing of "P_PLNGS_SYS_ID" | "P_MBR_ALT_ID" where "C8_LI_P_PROC_CD" has a value of D2950 (highlighted red) but no D2740 (highlighted green).  

So visually I need to report on where the pivot table has reds but no greens (all greens are ok, as are greens and reds combined).  In the pivot table the first trigger is at row 35 (for PPT8TCPD | M6MMMC768T because D2950 is present but there's no D2740).

That's what I need to be able to isolate and report on (ideally identify the total but also list out other info such as the "C_T_NUM"

Thanks!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Please follow these steps and let me know if this is what you are trying to achieve...

Click inside the Pivot Table --> Design Tab --> Report Layout --> Choose "Show in Tabular Form".
Then apply a filter on the field "C8_LI_R_PROC_CD" and uncheck the item D2740 and click OK.

Refer to the attached for more details. I have also disabled the Sub-Totals, if you need them, you can enable them again.
EE-Codes.xlsx
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I guess I misunderstood your requirement.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I have inserted a helper column "Both D2950 & D2740" (Column O) on the Data tab, changed the Source Data for the Pivot Table and added the helper column as a Page field and then applied a filter for True.

Let me know if this works as desired now.
EE-Codes-v2.xlsx

Author

Commented:
Hello, thank you for the reply.  That gets it closer as I can filter the new column to no, but it will still display entries for when only D2740 is present (I want to exclude if only D2740 is present); I was trying to quantify/analyze/isolate when only D2950 is present for a pairing:

Include if only D2950 present
Exclude if D2950 & D2740 present
Exclude if only D2740 present
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Include if only D2950 present
Exclude if D2950 & D2740 present
Exclude if only D2740 present

See if this works for you.
EE-Codes-v3.xlsx

Author

Commented:
Thank you, I can probably get this to work.  There are other code combinations and a host of scenarios in the actual data extract so using <>D2950 is  problematic.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Kevin!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial