Avatar of Kevin Buckland
Kevin Buckland
Flag for United States of America asked on

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

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
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Rob Henson

A sample file would make it much easier to understand your requirement.
Kevin Buckland

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"

Subodh Tiwari (Neeraj)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Subodh Tiwari (Neeraj)

I guess I misunderstood your requirement.
Subodh Tiwari (Neeraj)

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.
Kevin Buckland

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
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Kevin Buckland

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)

You're welcome Kevin!