Duplicate Billing

[Kay_T1060]

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

[#2]

@Kay_T1060 

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.

[#3]

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 based

[#4]

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

[#5]

@Abiola1

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. 

[#6]

@Kay_T1060 

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

[#7]

@Abiola1 

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 

[#8]

@Kay_T1060 

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. 

[#9]

@Abiola1 

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!

[#10 · Accepted solution]

Glad to be of help