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
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
Rob Henson
A sample file would make it much easier to understand your requirement.
ASKER
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!
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!
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
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
I guess I misunderstood your requirement.
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
Let me know if this works as desired now.
EE-Codes-v2.xlsx
ASKER
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
Include if only D2950 present
Exclude if D2950 & D2740 present
Exclude if only D2740 present
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
You're welcome Kevin!