Help with date formula???

MHodgin1016

New Member
Joined
Aug 9, 2017
Messages
7
I'm trying to build a PivotTable that will show us how many total units each of our clients has in their authorizations, how many units have been used, and how many are left. Every two weeks our payer sends us an "Auth Dump" that includes the client ID, authorization ID #, service name, start date of auth, end date of auth, and total units authorized. They also send us a "Claim Dump" which includes the client ID, date of service, service name, units billed, and if the billing was approved. I import both of these text files into separate workbooks.

I need to add a column to the Claim Dump table that indicates the authorization ID # associated with each claim. This would be found by matching the client ID # and service name on the Auth Dump and Claim Dump tables, then assessing which auth start and end date from the Auth Dump contains the date of service on the Claim Dump.

Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

MHodgin1016

New Member
Joined
Aug 9, 2017
Messages
7
Maybe this will help things be a little clearer about what I need. I have the following two tables on separate worksheets:

Excel Workbook
ABCDEFGH
1Patient_IDAuthorization_IDService_codeStart_dateEnd_dateAuth_unitsUnits_usedUnits_Remaining
211111234H20131/1/201712/31/20176580
311118964S51501/1/201712/31/20171200
422225678H20133/1/20172/28/20187460
533339101H20134/1/20173/31/20188420
633336791S51254/1/20173/31/20185241
744441121S51505/1/20174/30/2018800
855553141H20136/1/20175/31/20184456
955557316S51506/20/20172/28/20186325
1066665161S51255/1/20174/30/20183712
1177777181H20134/24/20173/31/2018500
Auth_Dump


Excel Workbook
ABCDEF
1Patient_IDService_codeDate_of_serviceStatusUnits_billedAuthorization_ID
21111H20133/18/2017Approved26
33333S51255/25/2017Approved18
45555H20136/26/2017Approved25
51111H20135/25/2017Approved34
61111S51502/16/2017Approved75
74444S51505/6/2017Approved20
86666S51255/18/2017Approved16
92222H20133/4/2017Approved16
107777H20135/15/2017Denied125
116666S51255/26/2017Approved14
125555H20136/2/2017Approved63
135555S51506/23/2017Approved58
142222H20134/14/2017Approved91
153333H20134/14/2017Approved64
163333S51254/15/2017Denied13
Claim_Dump


I need a formula in the Claim_Dump table that populates the correct authorization ID from the Auth_Dump table. A client may have multiple authorizations for the same service, but the dates should not overlap. Then I need a formula in the Auth_Dump table that sums the approved claims for that authorization ID.

I've been playing around with IF and INDEX MATCH MATCH, but so far no luck. Any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,812
Messages
5,446,635
Members
405,412
Latest member
Raghav Chamadiya

This Week's Hot Topics

Top