1 | This is what the claim data looks like: | ||||
|---|---|---|---|---|---|
2 | Claim | Insurance Denial Reason | |||
3 | 1 | 199-provider not enrolled; 189-no auth; 250-this procedure requires an authorization | |||
4 | 2 | 167-unspecified diagnosis not covered | |||
5 | 3 | 167-unspecified diagnosis not covered; 179-patient age | |||
6 | 4 | 153-procedure considered experimental; 179-patient age | |||
7 | 5 | 250-this procedure requires an authorization; 153-procedure considered experimental; 199-provider not enrolled | |||
8 | |||||
9 | This is how I've categorized the denials: | ||||
10 | Denial Code | Types of Denials | |||
11 | 199 | enrollment | |||
12 | 189 | prior auth | |||
13 | 250 | prior auth | |||
14 | 167 | billing | |||
15 | 179 | billing | |||
16 | 153 | billing | |||
17 | |||||
18 | This is what I want the data to look like after I run the excel formula: | ||||
19 | Claim | Insurance Denial Reason | enrollment | prior auth | billing |
20 | 1 | 199-provider not enrolled; 189-no auth; 250-this procedure requires an authorization | enrollment | prior auth | |
21 | 2 | 167-unspecified diagnosis not covered | billing | ||
22 | 3 | 167-unspecified diagnosis not covered; 179-patient age | billing | ||
23 | 4 | 153-procedure considered experimental; 179-patient age | billing | ||
24 | 5 | 250-this procedure requires an authorization; 153-procedure considered experimental; 199-provider not enrolled | enrollment | prior auth | billing |
25 | Excel formula for each denial category: | "=search("(=vlookup(B20,A11:B11,2,false))",B20)" | "=search("(=vlookup(B20,A12:B13,2,false))",B20)" | "=search("(=vlookup(B20,A14:B16,2,false))",B20)" | |