Report when values are on same day based on multiple criteria

[kbuckland]

Hello,

I'm scratching my head over an Excel problem and need someone smarter than me. I have a large dataset that I want to analyze and identify [for each pairing of Member (Col B / "B_MBR_ALT_ID") and billing provider (Col M / "P_BLNG_SYS_ID") and same date of service (Col H / C_SVC_FIRST_DT) there's a value of D2950 in Column E ("C8_OLI_R_PROC_CD") as well as at least one other code in Column E for that date from the list in named region "CrownEXcore" (which is in tab "0-21Crown").

I added a helper column (See Col U) that looks up if the code in Col E for that row contains one of the codes I'm looking for.

So in the pivot table example I want to be able efficiently find the entries for Charlie Brown (Row 33:43) because D2950 and D2740 (a code in the named region) both occurred on the same date, in this case 2/27/2018. However for Jayson Borne although he had both those same codes, we don't need to know because they were on separate days (1/31/19 & 2/27/19 -- see rows 147:168).


Thank you

[JeteMc]

Hello kbuckland and Welcome to Excel Forum.
This proposal adds two more helper columns to the data sheet, then all three helpers are included in the source data for the pivot table (PT2)
Column U in the file attached to post #1 is moved to column N.
Column O is populated using: =IF(N2="Yes",COUNTIFS(B$2:B$156,B2,E$2:E$156,'0-21Crown'!A$29,G$2:G$156,G2),0)
Column P is populated using: =COUNTIFS(B$2:B$156,B2,G$2:G$156,G2,N$2:N$156,"Yes",O$2:O$156,">0")>0
In the pivot table the results of column P are used as the filter (or could be used as a slicer). So to get the results you want you would set the filter to true.
Let us know if you have any questions.