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
<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
<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?
Auth_Dump
A | B | C | D | E | F | G | H | |
1 | Patient_ID | Authorization_ID | Service_code | Start_date | End_date | Auth_units | Units_used | Units_Remaining |
2 | 1111 | 1234 | H2013 | 1/1/2017 | 12/31/2017 | 6580 | ||
3 | 1111 | 8964 | S5150 | 1/1/2017 | 12/31/2017 | 1200 | ||
4 | 2222 | 5678 | H2013 | 3/1/2017 | 2/28/2018 | 7460 | ||
5 | 3333 | 9101 | H2013 | 4/1/2017 | 3/31/2018 | 8420 | ||
6 | 3333 | 6791 | S5125 | 4/1/2017 | 3/31/2018 | 5241 | ||
7 | 4444 | 1121 | S5150 | 5/1/2017 | 4/30/2018 | 800 | ||
8 | 5555 | 3141 | H2013 | 6/1/2017 | 5/31/2018 | 4456 | ||
9 | 5555 | 7316 | S5150 | 6/20/2017 | 2/28/2018 | 6325 | ||
10 | 6666 | 5161 | S5125 | 5/1/2017 | 4/30/2018 | 3712 | ||
11 | 7777 | 7181 | H2013 | 4/24/2017 | 3/31/2018 | 500 |
<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
A | B | C | D | E | F | |
1 | Patient_ID | Service_code | Date_of_service | Status | Units_billed | Authorization_ID |
2 | 1111 | H2013 | 3/18/2017 | Approved | 26 | |
3 | 3333 | S5125 | 5/25/2017 | Approved | 18 | |
4 | 5555 | H2013 | 6/26/2017 | Approved | 25 | |
5 | 1111 | H2013 | 5/25/2017 | Approved | 34 | |
6 | 1111 | S5150 | 2/16/2017 | Approved | 75 | |
7 | 4444 | S5150 | 5/6/2017 | Approved | 20 | |
8 | 6666 | S5125 | 5/18/2017 | Approved | 16 | |
9 | 2222 | H2013 | 3/4/2017 | Approved | 16 | |
10 | 7777 | H2013 | 5/15/2017 | Denied | 125 | |
11 | 6666 | S5125 | 5/26/2017 | Approved | 14 | |
12 | 5555 | H2013 | 6/2/2017 | Approved | 63 | |
13 | 5555 | S5150 | 6/23/2017 | Approved | 58 | |
14 | 2222 | H2013 | 4/14/2017 | Approved | 91 | |
15 | 3333 | H2013 | 4/14/2017 | Approved | 64 | |
16 | 3333 | S5125 | 4/15/2017 | Denied | 13 |
<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?