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
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

<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>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Claim_Dump

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

<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>
</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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
1Auth_Dump
2Patient_IDAuthorisation_IDService_codeStart_dateEnd_date
311111234H20131/1/201712/31/2017
411118964S51501/1/201712/31/2017
51111100000S51501/1/201812/31/2019
622225678H20133/1/20172/28/2018
733339101H20134/1/20173/31/2018
833336791S51254/1/20173/31/2018
944441121S51505/1/20174/30/2018
1055553141H20136/1/20175/31/2018
1155557316S51506/20/20172/28/2018
1266665161S51255/1/20174/30/2018
1377777181H20134/24/20173/31/2018
14
15Claim_Dump
16Patient_IDService_codeDate_of_serviceStatusUnits_billedAuthorisation_IDApproved_for AuthID
171111H20133/18/2017Approved26123460
183333S51255/25/2017Approved18679131
195555H20136/26/2017Approved25314188
201111H20135/25/2017Approved34123460
211111S51502/16/2017Approved75896475
221111S51502/17/2018Approved76001000007600
234444S51505/6/2017Approved20112120
246666S51255/18/2017Approved16516130
252222H20133/4/2017Approved165678107
267777H20135/15/2017Denied1257181125
276666S51255/26/2017Approved14516130
285555H20136/2/2017Approved63314188
295555S51506/23/2017Approved58731658
302222H20134/14/2017Approved915678107
313333H20134/14/2017Approved64910164
323333S51254/15/2017Denied13679131

<tbody>
</tbody>
Sheet23​

Worksheet Formulas
CellFormula
F17=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)))
G17=SUMIFS($E$17:$E$32,$F$17:$F$32,F17)

<tbody>
</tbody>

<tbody>
</tbody>
 
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
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
825
gvsumich
G
S
  • Question
How to click a link inside a table using VBA
Replies
0
Views
2K
swapnilk
S
M
  • 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
397
Peter_SSs
Peter_SSs
M
  • Question
Matching cells, comparing values and producing total
Replies
3
Views
343
JamesCanale
JamesCanale

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

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