Forum Discussion
Duplicate Billing
Hi Everyone,
I am looking to create a formula that will identify duplicate billing of some claims I am working with.
What I am looking for the formula to do is match the same patient on the same date of service that has a claim paid by different providers.
***Duplicate billing on optical products, identify different providers billing for the same member on the same DOS for procedure code V2020 (Basic Frames). ****
| Provider ID | Billing Provider ID | Provider Number | Provider Tax ID | Provider NPI Number | Provider First Name | Provider Last Name | Provider Facility Number | Provider Facility Name | Patient ID | Patient First Name | Patient Last Name | DOS Begin | Received Date |
| 112345.C123 | 112345.C123 | 112345 | 411234556 | 1999087650 | PEARLE VISION CENTER | C123 | PEARLE VISION CENTER | 00011111100 | TEXAS | BEASTER | 05-15-2019 | 05-15-2019 | |
| 123456.E234 | 123456.E234 | 123456 | 417765439 | 1999087654 | EYE CRAZY OPTICAL INC | E456 | EYE CRAZY OPTICAL | 00022222200 | TEXAS | BEASTER | 05-15-2019 | 05-20-2019 | |
| 134567.K345 | 134567.K345 | 134567 | 200098766 | 1999087655 | ASHTON D | COLEIO | K789 | STRATEGIC MANG | 00033333300 | ARSEN | FORENZ | 09-26-2019 | 09-26-2019 |
| 145678.E456 | 145678.E456 | 145678 | 990876543 | 1999876467 | EYE CRAZY OPTICAL INC | E890 | EYE CRAZY OPTICAL | 00044444400 | GLITZ | FANCY | 09-27-2019 | 09-27-2019 | |
| 156789.K567 | 156789.K567 | 156789 | 333444555 | 1999876568 | ASHTON D | COLEIO | K098 | STRATEGIC MANG | 00055555500 | GLITZ | FANCY | 10-21-2019 | 10-21-2019 |
| 167890.E678 | 167890.E678 | 167890 | 009988776 | 1999112345 | EYE CRAZY OPTICAL INC | E765 | EYE CRAZY OPTICAL | 00066666600 | GLITZ | FANCY | 10-21-2019 | 10-23-2019 |
- Glad to be of help
9 Replies
- COUNTIFS function can help you identify the duplicate values.
Assuming you want to find duplicate values in Provide Number in column C, you can have an helper column and execute the formula =COUNTIFS(C2:C100,C2)>1.
Click Enter and copy the formula down.. The values that are duplicated will return TRUE and unique Provider Number will return FALSE.
Another way of identify duplicate values is to use the Conditional Formatting.. Although, this is bot formula basedHi and thank you for your help. If I am understanding correctly, the result TRUE will be the duplicate billed items and the result FALSE will essentially be the claims that are billed correctly?
I have added a sample of the data that I am actually working with.
I am in the end result looking for duplicate billing. The "correct" billing provider is EYE KRAZY OPTICAL. I am looking to identify the additional provider's billing for the same member on the same date of service as EYE KRAZY OPTICAL.
I hope I didn't confuse you. I am known to overthink things.
It's hard to draw a connection between your description and the image of the data you provided. You say What I am looking for the formula to do is match the same patient on the same date of service that has a claim paid by different providers, but in what you show there's no column that specifies a date of service; nor is there a patient ID. Finally, there's a lot of different info on Providers, so it would help to know which of those columns are relevant.
Is it possible to upload a sample of the actual spreadsheet? Just make sure no real people (or, for that matter, real providers) can be identified.
mathetes Hi,
I have attached the data that I am working with. It's a small sample, but is exactly formatted to what I am working on.
I am in the end result looking for duplicate billing. The "correct" billing provider is EYE KRAZY OPTICAL. I am looking to identify the additional provider's billing for the same member on the same date of service as EYE KRAZY OPTICAL.
I am probably making this harder than what it is lol, but I am trying to work on my formulas.
