troubleshooting Question

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

Avatar of Kevin Buckland
Kevin BucklandFlag for United States of America asked on
VBAMicrosoft ExcelMicrosoft Office
9 Comments1 Solution113 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros