Units used tracking formula

M

MHodgin1016

New Member
Joined
Aug 9, 2017
Messages
7
I have imported data into Excel (same workbook, different sheets) from two text files as follows:

Auth_Dump

ABCDEFGH
Patient_IDAuthorization_IDService_codeStart_dateEnd_dateAuth_unitsUnits_usedUnits_Remaining
H2013
S5150
H2013
H2013
S5125
S5150
H2013
S5150
S5125
H2013

<colgroup><col style="width: 30px;"><col style="width: 116px;"><col style="width: 176px;"><col style="width: 149px;"><col style="width: 117px;"><col style="width: 110px;"><col style="width: 122px;"><col style="width: 129px;"><col style="width: 182px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]1234[/TD]

[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"]6580[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]8964[/TD]

[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"]1200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]2222[/TD]
[TD="align: right"]5678[/TD]

[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]7460[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]3333[/TD]
[TD="align: right"]9101[/TD]

[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]8420[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]3333[/TD]
[TD="align: right"]6791[/TD]

[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]5241[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]4444[/TD]
[TD="align: right"]1121[/TD]

[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"]800[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]5555[/TD]
[TD="align: right"]3141[/TD]

[TD="align: right"]6/1/2017[/TD]
[TD="align: right"]5/31/2018[/TD]
[TD="align: right"]4456[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]5555[/TD]
[TD="align: right"]7316[/TD]

[TD="align: right"]6/20/2017[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]6325[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: right"]6666[/TD]
[TD="align: right"]5161[/TD]

[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"]3712[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: right"]7777[/TD]
[TD="align: right"]7181[/TD]

[TD="align: right"]4/24/2017[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]500[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Claim_Dump

ABCDEF
Patient_IDService_codeDate_of_serviceStatusUnits_billedAuthorization_ID
H2013Approved
S5125Approved
H2013Approved
H2013Approved
S5150Approved
S5150Approved
S5125Approved
H2013Approved
H2013Denied
S5125Approved
H2013Approved
S5150Approved
H2013Approved
H2013Approved
S5125Denied

<colgroup><col style="width: 30px;"><col style="width: 116px;"><col style="width: 149px;"><col style="width: 172px;"><col style="width: 92px;"><col style="width: 130px;"><col style="width: 176px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]3/18/2017[/TD]

[TD="align: right"]26[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"]5/25/2017[/TD]

[TD="align: right"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]5555[/TD]

[TD="align: right"]6/26/2017[/TD]

[TD="align: right"]25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]5/25/2017[/TD]

[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]2/16/2017[/TD]

[TD="align: right"]75[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]4444[/TD]

[TD="align: right"]5/6/2017[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]6666[/TD]

[TD="align: right"]5/18/2017[/TD]

[TD="align: right"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: right"]2222[/TD]

[TD="align: right"]3/4/2017[/TD]

[TD="align: right"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: right"]7777[/TD]

[TD="align: right"]5/15/2017[/TD]

[TD="align: right"]125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: right"]6666[/TD]

[TD="align: right"]5/26/2017[/TD]

[TD="align: right"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD="align: right"]5555[/TD]

[TD="align: right"]6/2/2017[/TD]

[TD="align: right"]63[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="align: right"]5555[/TD]

[TD="align: right"]6/23/2017[/TD]

[TD="align: right"]58[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]
[TD="align: right"]2222[/TD]

[TD="align: right"]4/14/2017[/TD]

[TD="align: right"]91[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"]4/14/2017[/TD]

[TD="align: right"]64[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"]4/15/2017[/TD]

[TD="align: right"]13[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Re: Units used tracking formula...need help!!

I think this might be what you're looking for. I added an item (in yellow, Row 5) for Patient 1111 with two Service code transactions S5150 to account for your statement that patients "...may have multiple authorizations for the same service, but the dates should not overlap." I interpreted that to mean that the Authorisation table might have a patient with more than one Authorisation with the same service code but different dates. Then, the formula in Column G sums up the Units_Billed figure for every line with the same Authorisation code in that table.

ABCDEFG
Auth_Dump
Patient_IDAuthorisation_IDService_codeStart_dateEnd_date
H2013
S5150
H2013
H2013
S5125
S5150
H2013
S5150
S5125
H2013
Claim_Dump
Patient_IDService_codeDate_of_serviceStatusUnits_billedAuthorisation_IDApproved_for AuthID
H2013Approved
S5125Approved
H2013Approved
H2013Approved
S5150Approved
S5150Approved
S5125Approved
H2013Approved
H2013Denied
S5125Approved
H2013Approved
S5150Approved
H2013Approved
H2013Approved
S5125Denied

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]1234[/TD]

[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]8964[/TD]

[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]1111[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]100000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]S5150[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]1/1/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]12/31/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2222[/TD]
[TD="align: right"]5678[/TD]

[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3333[/TD]
[TD="align: right"]9101[/TD]

[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3333[/TD]
[TD="align: right"]6791[/TD]

[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4444[/TD]
[TD="align: right"]1121[/TD]

[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]5555[/TD]
[TD="align: right"]3141[/TD]

[TD="align: right"]6/1/2017[/TD]
[TD="align: right"]5/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]5555[/TD]
[TD="align: right"]7316[/TD]

[TD="align: right"]6/20/2017[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6666[/TD]
[TD="align: right"]5161[/TD]

[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]4/30/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]7777[/TD]
[TD="align: right"]7181[/TD]

[TD="align: right"]4/24/2017[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]3/18/2017[/TD]

[TD="align: right"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]60[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"]5/25/2017[/TD]

[TD="align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6791[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]31[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]5555[/TD]

[TD="align: right"]6/26/2017[/TD]

[TD="align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3141[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]88[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]5/25/2017[/TD]

[TD="align: right"]34[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1234[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]60[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]2/16/2017[/TD]

[TD="align: right"]75[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8964[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]75[/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]1111[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]S5150[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]2/17/2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] "]Approved[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: right"]7600[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]7600[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]4444[/TD]

[TD="align: right"]5/6/2017[/TD]

[TD="align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1121[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]20[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]6666[/TD]

[TD="align: right"]5/18/2017[/TD]

[TD="align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5161[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]30[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]2222[/TD]

[TD="align: right"]3/4/2017[/TD]

[TD="align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5678[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]107[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]7777[/TD]

[TD="align: right"]5/15/2017[/TD]

[TD="align: right"]125[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7181[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]125[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]6666[/TD]

[TD="align: right"]5/26/2017[/TD]

[TD="align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5161[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]30[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]5555[/TD]

[TD="align: right"]6/2/2017[/TD]

[TD="align: right"]63[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3141[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]88[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]5555[/TD]

[TD="align: right"]6/23/2017[/TD]

[TD="align: right"]58[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7316[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]58[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]2222[/TD]

[TD="align: right"]4/14/2017[/TD]

[TD="align: right"]91[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5678[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]107[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"]4/14/2017[/TD]

[TD="align: right"]64[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]9101[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]64[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"]4/15/2017[/TD]

[TD="align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6791[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]31[/TD]

</tbody>
Sheet23​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F17[/TH]
[TD="align: left"]=INDEX($B$3:$B$13,SUMPRODUCT((A17&B17=$A$3:$A$13&$C$3:$C$13)*(C17>=$D$3:$D$13)*(C17<=$E$3:$E$13)*(ROW($A$3:$A$13)-ROW($A$3)+1)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G17[/TH]
[TD="align: left"]=SUMIFS($E$17:$E$32,$F$17:$F$32,F17)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Units used tracking formula...need help!!

Hi

Another option using DRSteele's layout. In F17:

=LOOKUP(2,1/($A$3:$A$13=A17)/($C$3:$C$13=B17)/($D$3:$D$13<=C17)/($E$3:$E$13>=C17),$B$3:$B$13)
 
Last edited:
Upvote 0
Re: Units used tracking formula...need help!!

Actually, I'm still having some issues with the G17 formula. Right now it's just copying the Units_billed column from the Claim Dump to the Approved_for AuthID column instead of totaling only the units for that particular authorization that are approved. I need it to only sum the units billed from the Claim Dump IF the authorization ID matches AND the claim was Approved. This is what I have so far but again it is just copying the Units column (except for lines that don't say Approved, those just have a 0):

=SUMIFS($E$17:$E$32,$F$17:$F$32,F17,$D$17:$D$32,"Approved")
 
Upvote 0
Re: Units used tracking formula...need help!!

Hi

Not clear to me.

Please point out in the table posted some values that are wrong, what should be the correct value and the logic you used to calculate them.
 
Upvote 0
Re: Units used tracking formula...need help!!

Here is what the tables should look like completed:

Code: 
[RANGE=cls:xl2bb-100][XR][XH=cs:7]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][/XR][XR][XH]1[/XH][XD=h:l]Patient_ID[/XD][XD=h:l]Service_code[/XD][XD=h:l]Date_of_service[/XD][XD=h:l]Status[/XD][XD=h:l]Units_billed[/XD][XD=h:l]Authorization_ID[/XD][/XR][XR][XH]2[/XH][XD=h:r]1111[/XD][XD=h:l]H2013[/XD][XD=h:r]3/18/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]26[/XD][XD=h:r]1234[/XD][/XR][XR][XH]3[/XH][XD=h:r]3333[/XD][XD=h:l]S5125[/XD][XD=h:r]5/25/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]18[/XD][XD=h:r]6791[/XD][/XR][XR][XH]4[/XH][XD=h:r]5555[/XD][XD=h:l]H2013[/XD][XD=h:r]6/26/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]25[/XD][XD=h:r]3141[/XD][/XR][XR][XH]5[/XH][XD=h:r]1111[/XD][XD=h:l]H2013[/XD][XD=h:r]5/25/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]34[/XD][XD=h:r]1234[/XD][/XR][XR][XH]6[/XH][XD=h:r]1111[/XD][XD=h:l]S5150[/XD][XD=h:r]2/16/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]75[/XD][XD=h:r]8964[/XD][/XR][XR][XH]7[/XH][XD=h:r]4444[/XD][XD=h:l]S5150[/XD][XD=h:r]5/6/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]20[/XD][XD=h:r]1121[/XD][/XR][XR][XH]8[/XH][XD=h:r]6666[/XD][XD=h:l]S5125[/XD][XD=h:r]5/18/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]16[/XD][XD=h:r]5161[/XD][/XR][XR][XH]9[/XH][XD=h:r]2222[/XD][XD=h:l]H2013[/XD][XD=h:r]3/4/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]16[/XD][XD=h:r]5678[/XD][/XR][XR][XH]10[/XH][XD=h:r]7777[/XD][XD=h:l]H2013[/XD][XD=h:r]5/15/2017[/XD][XD=h:l]Denied[/XD][XD=h:r]125[/XD][XD=h:r]7181[/XD][/XR][XR][XH]11[/XH][XD=h:r]6666[/XD][XD=h:l]S5125[/XD][XD=h:r]5/26/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]14[/XD][XD=h:r]5161[/XD][/XR][XR][XH]12[/XH][XD=h:r]5555[/XD][XD=h:l]H2013[/XD][XD=h:r]6/2/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]63[/XD][XD=h:r]3141[/XD][/XR][XR][XH]13[/XH][XD=h:r]5555[/XD][XD=h:l]S5150[/XD][XD=h:r]6/23/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]58[/XD][XD=h:r]7316[/XD][/XR][XR][XH]14[/XH][XD=h:r]2222[/XD][XD=h:l]H2013[/XD][XD=h:r]4/14/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]91[/XD][XD=h:r]5678[/XD][/XR][XR][XH]15[/XH][XD=h:r]3333[/XD][XD=h:l]H2013[/XD][XD=h:r]4/14/2017[/XD][XD=h:l]Approved[/XD][XD=h:r]64[/XD][XD=h:r]9101[/XD][/XR][XR][XH]16[/XH][XD=h:r]3333[/XD][XD=h:l]S5125[/XD][XD=h:r]4/15/2017[/XD][XD=h:l]Denied[/XD][XD=h:r]13[/XD][XD=h:r]6791[/XD][/XR][XR][XH=cs:7][RANGE][XR][XD]Claim_Dump[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
Code: 
[RANGE=cls:xl2bb-100][XR][XH=cs:9]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]1[/XH][XD=h:l]Patient_ID[/XD][XD=h:l]Authorization_ID[/XD][XD=h:l]Service_code[/XD][XD=h:l]Start_date[/XD][XD=h:l]End_date[/XD][XD=h:l]Auth_units[/XD][XD=h:l]Units_used[/XD][XD=h:l]Units_Remaining[/XD][/XR][XR][XH]2[/XH][XD=h:r]1111[/XD][XD=h:r]1234[/XD][XD=h:l]H2013[/XD][XD=h:r]1/1/2017[/XD][XD=h:r]12/31/2017[/XD][XD=h:r]6580[/XD][XD=h:r]60[/XD][XD=h:r]6520[/XD][/XR][XR][XH]3[/XH][XD=h:r]1111[/XD][XD=h:r]8964[/XD][XD=h:l]S5150[/XD][XD=h:r]1/1/2017[/XD][XD=h:r]12/31/2017[/XD][XD=h:r]1200[/XD][XD=h:r]75[/XD][XD=h:r]1125[/XD][/XR][XR][XH]4[/XH][XD=h:r]2222[/XD][XD=h:r]5678[/XD][XD=h:l]H2013[/XD][XD=h:r]3/1/2017[/XD][XD=h:r]2/28/2018[/XD][XD=h:r]7460[/XD][XD=h:r]107[/XD][XD=h:r]7353[/XD][/XR][XR][XH]5[/XH][XD=h:r]3333[/XD][XD=h:r]9101[/XD][XD=h:l]H2013[/XD][XD=h:r]4/1/2017[/XD][XD=h:r]3/31/2018[/XD][XD=h:r]8420[/XD][XD=h:r]64[/XD][XD=h:r]8356[/XD][/XR][XR][XH]6[/XH][XD=h:r]3333[/XD][XD=h:r]6791[/XD][XD=h:l]S5125[/XD][XD=h:r]4/1/2017[/XD][XD=h:r]3/31/2018[/XD][XD=h:r]5241[/XD][XD=h:r]18[/XD][XD=h:r]5223[/XD][/XR][XR][XH]7[/XH][XD=h:r]4444[/XD][XD=h:r]1121[/XD][XD=h:l]S5150[/XD][XD=h:r]5/1/2017[/XD][XD=h:r]4/30/2018[/XD][XD=h:r]800[/XD][XD=h:r]20[/XD][XD=h:r]780[/XD][/XR][XR][XH]8[/XH][XD=h:r]5555[/XD][XD=h:r]3141[/XD][XD=h:l]H2013[/XD][XD=h:r]6/1/2017[/XD][XD=h:r]5/31/2018[/XD][XD=h:r]4456[/XD][XD=h:r]88[/XD][XD=h:r]4368[/XD][/XR][XR][XH]9[/XH][XD=h:r]5555[/XD][XD=h:r]7316[/XD][XD=h:l]S5150[/XD][XD=h:r]6/20/2017[/XD][XD=h:r]2/28/2018[/XD][XD=h:r]6325[/XD][XD=h:r]58[/XD][XD=h:r]6267[/XD][/XR][XR][XH]10[/XH][XD=h:r]6666[/XD][XD=h:r]5161[/XD][XD=h:l]S5125[/XD][XD=h:r]5/1/2017[/XD][XD=h:r]4/30/2018[/XD][XD=h:r]3712[/XD][XD=h:r]30[/XD][XD=h:r]3682[/XD][/XR][XR][XH]11[/XH][XD=h:r]7777[/XD][XD=h:r]7181[/XD][XD=h:l]H2013[/XD][XD=h:r]4/24/2017[/XD][XD=h:r]3/31/2018[/XD][XD=h:r]500[/XD][XD=h:r]0[/XD][XD=h:r]500[/XD][/XR][XR][XH=cs:9][RANGE][XR][XD]Auth_Dump[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
 
Upvote 0
Re: Units used tracking formula...need help!!

Sorry I don't know why the tables are so compressed. Here they are again:

Excel Workbook
ABCDEFGH
1Patient_IDAuthorization_IDService_codeStart_dateEnd_dateAuth_unitsUnits_usedUnits_Remaining
211111234H20131/1/201712/31/20176580606520
311118964S51501/1/201712/31/20171200751125
422225678H20133/1/20172/28/201874601077353
533339101H20134/1/20173/31/20188420648356
633336791S51254/1/20173/31/20185241185223
744441121S51505/1/20174/30/201880020780
855553141H20136/1/20175/31/20184456884368
955557316S51506/20/20172/28/20186325586267
1066665161S51255/1/20174/30/20183712303682
1177777181H20134/24/20173/31/20185000500
Auth_Dump


Excel Workbook
ABCDEF
1Patient_IDService_codeDate_of_serviceStatusUnits_billedAuthorization_ID
21111H20133/18/2017Approved261234
33333S51255/25/2017Approved186791
45555H20136/26/2017Approved253141
51111H20135/25/2017Approved341234
61111S51502/16/2017Approved758964
74444S51505/6/2017Approved201121
86666S51255/18/2017Approved165161
92222H20133/4/2017Approved165678
107777H20135/15/2017Denied1257181
116666S51255/26/2017Approved145161
125555H20136/2/2017Approved633141
135555S51506/23/2017Approved587316
142222H20134/14/2017Approved915678
153333H20134/14/2017Approved649101
163333S51254/15/2017Denied136791
Claim_Dump


I need the Units_used column on the Auth Dump table to only sum units billed on the Claim Dump table for the matching authorization that are Approved. The current formula just copies the Units_billed column instead of summing the correct rows.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
S
  • Question Question
How to click a link inside a table using VBA
Replies
0
Views
2K
swapnilk
S
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
593
Peter_SSs
Peter_SSs
M
  • Question Question
Matching cells, comparing values and producing total
Replies
3
Views
441
JamesCanale
JamesCanale

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top