Feb 26 2020 05:10 PM
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 |
Feb 26 2020 07:10 PM
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.
Feb 26 2020 09:55 PM
Feb 27 2020 06:52 AM
@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.
Feb 27 2020 06:59 AM
Hi 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.
Feb 27 2020 07:24 AM
Absolutely yes. The result TRUE is the duplicate billed why FALSE is the otherwise. I have opened your workbook and saw many a handful of columns. I need to know which particular column (either column G or I) you're referring to
Feb 27 2020 07:39 AM
Hi,
This is wonderful. I am referring to Column G, but I would like to use Column C because I am going by the billing provider versus the provider's name or location.
Thank you again
Feb 27 2020 07:56 AM
Based on column C, I created a new column and executed the formula as seen in the caption below
Duplicate Provider Number returned TRUE while unique numbers returned FALSE.
Feb 27 2020 08:10 AM
Hi,
Thank you very much for your help and helping me find a faster way to complete the task I am doing!
Have a great day!
Feb 27 2020 08:11 AM
Solution