Duplicate Conditional Formatting with non-exact search inquiry

[ashtons]

Hello,

I'm not especially experienced with Excel, and haven't been able to find someone with my situation online to help troubleshoot my problem.

It's a complicated situation in a somewhat specialized field, so I'll try to explain as best as possible.

I work in medical billing, and there is a specific insurance payer who has strict rules about what ICD 10 diagnosis codes are allowed to be billed together (i.e. the code for chronic pain basically excludes any other pain codes, etc. meaning if a chronic pain code and a leg pain code are billed together, the insurance will deny.) We have a running list of codes that can't be billed together, but I wanted to make an easier "lookup" style excel sheet where we could essentially 1. input all the codes on the claim and have the sheet highlight codes that have exclusions and 2. search codes one at a time to isolate codes that exist on the claim that need removal because they can't be billed with the searched code.

The first part I've managed just fine with conditional formatting and a COUNTIF formula, so any codes on the claim that are "headers" in the table of codes and exclusions highlight red.

The second part is where I've run into trouble. ICD 10 codes exist in a range of fewer digits/less specific to more digits/more specific (For example, M17 (osteoarthritis of knee) at its least specific, to M17.32 (unilateral post-traumatic osteoarthritis, left knee). Some codes have up to 7 digits, and dozens if not hundreds of variations/sub codes). But, in the case of codes that are mutually exclusive, if a code excludes M17, then any codes more specific (M17.0, M17.32, etc.) are also excluded. The claims usually have more specific codes, but the table of exclusions usually has the least specific code that is still an exclusion. Since conditional formatting doesn't like wildcards, I've found that the hidden helper table will highlight duplicates using the wildcard, but the more specific codes in the column I'm actually trying to format will not highlight. Is there a way to finagle this to make it work? (Preferably without listing out every single code under the least specific code).

Here's the sheet, I'm hoping I've formatted this as well as explained it correctly. Thanks in advance

Colvin diagnosis exclusion lookup.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
2	DX CODES ON YOUR CLAIM	DX code excluded by search term (this colum is supposed to be hidden!)->	*ONE* RED DX CODE HERE		CODES
3	M81.0	F45.41	g89.4		G89.21	G89.29	G89.3	G89.4	I73.89	K08.89	K86.3	M15.0	M25.551	M25.552	M25.561	M25.562	M54.08	M54.12	M54.16	M54.2	M54.59	M54.6	M79.641	M79.642	M81.0	R25.2	R53.1	R53.81	S88.112D	S88.112S	Z79.891
4	G89.4	G43.*		Codes they exclude	F45.41	F45.41	F45.41	F45.41	E08.51	M26.*	D13.7	M16.*	M20.*	M20.*	M20.*	M20.*	F45.41	H45.41	F45.41	F45.41	F45.41	F45.41	F45.41	F45.41	M80.*	F95.*	M62.81	F33	S98.*	S98.*	F1*
5	R53.81	G44.*			G43.*	G43.*	G43.*	G43.*	E08.52	M27.*	E84.*	M17.*	M21.*	M21.*	M21.*	M21.*	L93.2	M47.2*	M47.2*	M50.*	M51.2*	M51.*	F45.8	F45.8	M89.0	F98.4	M62.84	F43.0			O99.32*
6	Z79.891	G50.1			G44.*	G44.*	G44.*	G44.*	E09.51		K90.3	M18.*	M26.6*	M26.6*	M26.6*	M26.6*	M35.6	M50.1*	M50.1		M54.4*		M25.5*	M25.5*		G2*	R54	O26.8*
7	m48.061	G54.6			G50.1	G50.1	G50.1	G50.1	E09.52			M19.*	M65.2*	M65.2*	M65.2*	M65.2*	M79.3	M51.1*	M51.1*		S39.012					G40.4
8	M25.561	H57.1			G54.6	G54.6	G54.6	G54.6	E10.51				M71.4*	M71.4*	M71.4*	M71.4*		M79.2*	M79.2*							M62.830
9	m25.562	H92.0			G56.4*	G56.4*	H57.1	H57.1	E10.52				M75.3*	M75.3*	M75.3*	M75.3*										M62.831
10	m25.532	K08.8			G57.7	G57.7*	H92.0*	H92.0	E11.51				M79.6*	M79.6*	M79.6*	M79.6*										R29.0
11	m54.12	K14.6	THIS MEANS I HAVE EXCLUSIONS AND YOU SHOULD SEARCH FOR ME!		G89.0	G89.0	K08.8	K08.8	E11.52				R26.*	R26.*	R26.*	R26.*
12	m54.16	M25.5*			G89.3	G89.3	K14.6	K14.6	E12.51
13	m54.59	M54.*			G89.4	G89.4	M25.5*	M25.5*	E12.52
14		M79.1*			G90.5*	G90.5*	M54.*	M54.*	E13.51
15		M79.6*			H57.1	H57.1	M79.1*	M79.1*	E13.52
16		N23			H92.0*	H92.0	M79.6*	M79.6*	G45.9
17		N64.4			K08.8	K08.8	M23	N23	T33.*
18		N94.81*	THIS MEANS I AM AN EXCLUSION AND YOU SHOULD DELETE ME!		K14.6	K14.6	M64.4	N64.4	T34.*
19		R07.*			M25.5*	M25.5*	N94.81*	N94.81*	T69.0*
20		R10.*			M54.*	M54.*	R07.*	R07.*	T69.1*
21		R30.9			M79.1*	M79.1*	R10.*	R10.*
22		R51.9			M79.6*	M79.6*	R30.9	R30.9
23		R52			N23	N23	R51.9	R51.9
24		T82.84			N64.4	N64.4	R52	R52
25		T83.84			N94.81*	N94.81*	T82.84	T82.84
26		T84.84			R07.*	R07.*	T83.84	T83.84
27		T85.84			R10.*	R10.*	T84.84	T84.84
28		0			R10.2	R10.2	T85.84*	T85.84
29		0			R30.9	R30.9
30		0			R51.9	R51.9
31		0			R52	R52
32		0			T82.84	T82.84
33		0			T83.84	T83.84
34		0			T84.84	T84.84
35					T85.84*	T85.84*
LOOKUP

Cell Formulas
Range	Formula
C3:C34	C3	=XLOOKUP(D3,F3:AF3,F4:AF35,"N/A",2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:C34	Cell Value	duplicates	text	NO
B3	Expression	=COUNTIF($F$3:$AF$3,B3)	text	NO
B4	Expression	=COUNTIF($F$3:$AF$3,B4)	text	NO
B5	Expression	=COUNTIF($F$3:$AF$3,B5)	text	NO
B6	Expression	=COUNTIF($F$3:$AF$3,B6)	text	NO
B7	Expression	=COUNTIF($F$3:$AF$3,B7)	text	NO
B8	Expression	=COUNTIF($F$3:$AF$3,B8)	text	NO
B9	Expression	=COUNTIF($F$3:$AF$3,B9)	text	NO
B10	Expression	=COUNTIF($F$3:$AF$3,B10)	text	NO
B11	Expression	=COUNTIF($F$3:$AF$3,B11)	text	NO
B12	Expression	=COUNTIF($F$3:$AF$3,B12)	text	NO
B13	Expression	=COUNTIF($F$3:$AF$3,B13)	text	NO
B14	Expression	=COUNTIF($F$3:$AF$3,B14)	text	NO
B15	Expression	=COUNTIF($F$3:$AF$3,B15)	text	NO
B16	Expression	=COUNTIF($F$3:$AF$3,B16)	text	NO
B17	Expression	=COUNTIF($F$3:$AF$3,B17)	text	NO
B18	Expression	=COUNTIF($F$3:$AF$3,B18)	text	NO
B19	Expression	=COUNTIF($F$3:$AF$3,B19)	text	NO
B20	Expression	=COUNTIF($F$3:$AF$3,B20)	text	YES
B21	Expression	=COUNTIF($F$3:$AF$3,B21)	text	NO
B22	Expression	=COUNTIF($F$3:$AF$3,B22)	text	NO
B23	Expression	=COUNTIF($F$3:$AF$3,B23)	text	NO
B24	Expression	=COUNTIF($F$3:$AF$3,B24)	text	NO
B25	Expression	=COUNTIF($F$3:$AF$3,B25)	text	NO
B26	Expression	=COUNTIF($F$3:$AF$3,B26)	text	NO
B27	Expression	=COUNTIF($F$3:$AF$3,B27)	text	NO
B28	Expression	=COUNTIF($F$3:$AF$3,B28)	text	NO
B29	Expression	=COUNTIF($F$3:$AF$3,B29)	text	NO
B30	Expression	=COUNTIF($F$3:$AF$3,B30)	text	NO
B31	Expression	=COUNTIF($F$3:$AF$3,B31)	text	NO
B32	Expression	=COUNTIF($F$3:$AF$3,B32)	text	NO
B33	Expression	=COUNTIF($F$3:$AF$3,B33)	text	NO
B34	Expression	=COUNTIF($F$3:$AF$3,B34)	text	NO

 

[arun_eagertolearn]

Hi

Please check whether this helps
I hope my understanding is clear

DX Codes are in column A
vlookup of Source

Three checks are being done
1. Presence in header row
2. Whether the individual entries in column A are present in exclusion list of the entries in column A (wild card match also included)
3. Not exactly a check- against a particular entry in column A, how many of the other entries are in the exclusion list
Col D highlights those exclusions against entries in Col A

Book12.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	DX CODES ON YOUR CLAIM	Check 1 Headings	Check 2 In exclusions	Which specific exclusion	Check 3 No of DX codes from the balance which are being duplicated		List of exclusions under the DX code in col A
2	M81.0	1	0		0		M80.*	M89.0
3	G89.4	1	0		0		F45.41	G43.*	G44.*	G50.1	G54.6	H57.1	H92.0	K08.8	K14.6	M25.5*	M54.*	M79.1*	M79.6*	N23	N64.4	N94.81*	R07.*	R10.*	R30.9	R51.9	R52	T82.84	T83.84	T84.84	T85.84
4	R53.81	1	0		0		F33	F43.0	O26.8*
5	Z79.891	1	0		0		F1*	O99.32*
6	m48.061	0	0		0
7	M25.561	1	1	M25.5*	0		M20.*	M21.*	M26.6*	M65.2*	M71.4*	M75.3*	M79.6*	R26.*
8	m25.562	1	1	M25.5*	0		M20.*	M21.*	M26.6*	M65.2*	M71.4*	M75.3*	M79.6*	R26.*
9	m25.532	0	1	M25.5*	0
10	m54.12	1	1	M54.*	0		H45.41	M47.2*	M50.1*	M51.1*	M79.2*
11	m54.16	1	1	M54.*	0		F45.41	M47.2*	M50.1	M51.1*	M79.2*
12	m54.59	1	1	M54.*	0		F45.41	M51.2*	M54.4*	S39.012
13
14		1 indicates duplicates, 0 implies no duplicate
Sheet4

Cell Formulas
Range	Formula
B2:B12	B2	=IF(ISNUMBER(MATCH(A2,Source!$E$2:$AE$2,0)),1,0)
C2:C12	C2	=SUM(IF(ISNUMBER(MATCH($G$2:$AE$12,TOROW(A2),0)),1,0))
D2:D12	D2	=FILTER(TOROW($G$2:$AE$12),TOROW(IF(ISNUMBER(MATCH($G$2:$AE$12,A2,0)),1,0))<>0,"")
E2	E2	=SUM(IF(ISNUMBER(MATCH(G2:AE2,D2:D12,0)),1,0))*IF(G2="",0,1)
E3	E3	=SUM(IF(ISNUMBER(MATCH(G3:AE3,D13:D13,0)),1,0))*IF(G3="",0,1)
E4	E4	=SUM(IF(ISNUMBER(MATCH(G4:AE4,D13:D14,0)),1,0))*IF(G4="",0,1)
E5	E5	=SUM(IF(ISNUMBER(MATCH(G5:AE5,D13:D15,0)),1,0))*IF(G5="",0,1)
E6	E6	=SUM(IF(ISNUMBER(MATCH(G6:AE6,D13:D16,0)),1,0))*IF(G6="",0,1)
E7	E7	=SUM(IF(ISNUMBER(MATCH(G7:AE7,D13:D17,0)),1,0))*IF(G7="",0,1)
E8	E8	=SUM(IF(ISNUMBER(MATCH(G8:AE8,D13:D18,0)),1,0))*IF(G8="",0,1)
E9	E9	=SUM(IF(ISNUMBER(MATCH(G9:AE9,D13:D19,0)),1,0))*IF(G9="",0,1)
E10	E10	=SUM(IF(ISNUMBER(MATCH(G10:AE10,D13:D20,0)),1,0))*IF(G10="",0,1)
E11	E11	=SUM(IF(ISNUMBER(MATCH(G11:AE11,D13:D21,0)),1,0))*IF(G11="",0,1)
E12	E12	=SUM(IF(ISNUMBER(MATCH(G12:AE12,D13:D22,0)),1,0))*IF(G12="",0,1)
G2:H2,G12:J12,G10:K11,G7:N8,G6,G9,G5:H5,G4:I4,G3:AE3	G2	=LET(a,TOROW(XLOOKUP(A2,Source!$E$2:$AE$2,Source!$E$3:$AE$34,0)),FILTER(a,a<>0,""))
Dynamic array formulas.

 

[ashtons]

Hi

Please check whether this helps
I hope my understanding is clear

DX Codes are in column A
vlookup of Source

Three checks are being done
1. Presence in header row
2. Whether the individual entries in column A are present in exclusion list of the entries in column A (wild card match also included)
3. Not exactly a check- against a particular entry in column A, how many of the other entries are in the exclusion list
Col D highlights those exclusions against entries in Col A

Book12.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	DX CODES ON YOUR CLAIM	Check 1 Headings	Check 2 In exclusions	Which specific exclusion	Check 3 No of DX codes from the balance which are being duplicated		List of exclusions under the DX code in col A
2	M81.0	1	0		0		M80.*	M89.0
3	G89.4	1	0		0		F45.41	G43.*	G44.*	G50.1	G54.6	H57.1	H92.0	K08.8	K14.6	M25.5*	M54.*	M79.1*	M79.6*	N23	N64.4	N94.81*	R07.*	R10.*	R30.9	R51.9	R52	T82.84	T83.84	T84.84	T85.84
4	R53.81	1	0		0		F33	F43.0	O26.8*
5	Z79.891	1	0		0		F1*	O99.32*
6	m48.061	0	0		0
7	M25.561	1	1	M25.5*	0		M20.*	M21.*	M26.6*	M65.2*	M71.4*	M75.3*	M79.6*	R26.*
8	m25.562	1	1	M25.5*	0		M20.*	M21.*	M26.6*	M65.2*	M71.4*	M75.3*	M79.6*	R26.*
9	m25.532	0	1	M25.5*	0
10	m54.12	1	1	M54.*	0		H45.41	M47.2*	M50.1*	M51.1*	M79.2*
11	m54.16	1	1	M54.*	0		F45.41	M47.2*	M50.1	M51.1*	M79.2*
12	m54.59	1	1	M54.*	0		F45.41	M51.2*	M54.4*	S39.012
13
14		1 indicates duplicates, 0 implies no duplicate
Sheet4

Cell Formulas
Range	Formula
B2:B12	B2	=IF(ISNUMBER(MATCH(A2,Source!$E$2:$AE$2,0)),1,0)
C2:C12	C2	=SUM(IF(ISNUMBER(MATCH($G$2:$AE$12,TOROW(A2),0)),1,0))
D2:D12	D2	=FILTER(TOROW($G$2:$AE$12),TOROW(IF(ISNUMBER(MATCH($G$2:$AE$12,A2,0)),1,0))<>0,"")
E2	E2	=SUM(IF(ISNUMBER(MATCH(G2:AE2,D2:D12,0)),1,0))*IF(G2="",0,1)
E3	E3	=SUM(IF(ISNUMBER(MATCH(G3:AE3,D13:D13,0)),1,0))*IF(G3="",0,1)
E4	E4	=SUM(IF(ISNUMBER(MATCH(G4:AE4,D13:D14,0)),1,0))*IF(G4="",0,1)
E5	E5	=SUM(IF(ISNUMBER(MATCH(G5:AE5,D13:D15,0)),1,0))*IF(G5="",0,1)
E6	E6	=SUM(IF(ISNUMBER(MATCH(G6:AE6,D13:D16,0)),1,0))*IF(G6="",0,1)
E7	E7	=SUM(IF(ISNUMBER(MATCH(G7:AE7,D13:D17,0)),1,0))*IF(G7="",0,1)
E8	E8	=SUM(IF(ISNUMBER(MATCH(G8:AE8,D13:D18,0)),1,0))*IF(G8="",0,1)
E9	E9	=SUM(IF(ISNUMBER(MATCH(G9:AE9,D13:D19,0)),1,0))*IF(G9="",0,1)
E10	E10	=SUM(IF(ISNUMBER(MATCH(G10:AE10,D13:D20,0)),1,0))*IF(G10="",0,1)
E11	E11	=SUM(IF(ISNUMBER(MATCH(G11:AE11,D13:D21,0)),1,0))*IF(G11="",0,1)
E12	E12	=SUM(IF(ISNUMBER(MATCH(G12:AE12,D13:D22,0)),1,0))*IF(G12="",0,1)
G2:H2,G12:J12,G10:K11,G7:N8,G6,G9,G5:H5,G4:I4,G3:AE3	G2	=LET(a,TOROW(XLOOKUP(A2,Source!$E$2:$AE$2,Source!$E$3:$AE$34,0)),FILTER(a,a<>0,""))
Dynamic array formulas.

Hi,

Thank you for your response. I think this is more or less what I've managed to do , but I'm having trouble integrating this into my excel program because of an error with the source in a few of the formulas. usually I either get an error message for a circular formula argument, or Excel crashes completely. Like I said I'm not familiar with Excel or XL2BB, so if there's something I'm meant to be doing to paste the mockup you've made into my native excel sheet please let me know.

If I'm understanding correctly, Column D will be what indicates which codes to remove (i.e. remove M25.562 in order to get C7=0, and remove M54.12, M54.16, and M54.59 to get C10=0). Is there a way to do this with conditional formatting to highlight the codes that need to be deleted? Or does the presence of the wildcards make this impossible?

Thanks.

 

[arun_eagertolearn]

Hi

The first two checks flag cases where an exclusion can get highlighted
Please do a conditional formatting on col A depending on these two columns (col B and C)

Cases like cell A9- m25.532 escape the first check but get highlighted in the second check

Yes, Column D indicates which is the entry in the column G to column AE section is getting highlighted as an exclusion against the entry in column A

For example, A7 is M25.561---and an exclusion is getting highlighted in C7
D7 tells which particular entry in from col G to col AE is causing that highlight in C7 to happen, which is an entry M25.5*

Col E tells how many exclusions from are there in the DX list (col A)
For example, E3=2, implying that two of the exclusions given in col G to AE are there amongst the other entries in col A
These are M25.5* and M54.* as can be seen in col D

Please do share your feedback whether this helps

Book12.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	DX CODES ON YOUR CLAIM	Check 1 Headings	Check 2 In exclusions	Which specific exclusion	Check 3 No of DX codes from the balance which are being duplicated		List of exclusions under the DX code in col A
2	M81.0	1	0		0		M80.*	M89.0
3	G89.4	1	0		2		F45.41	G43.*	G44.*	G50.1	G54.6	H57.1	H92.0	K08.8	K14.6	M25.5*	M54.*	M79.1*	M79.6*	N23	N64.4	N94.81*	R07.*	R10.*	R30.9	R51.9	R52	T82.84	T83.84	T84.84	T85.84
4	R53.81	1	0		0		F33	F43.0	O26.8*
5	Z79.891	1	0		0		F1*	O99.32*
6	m48.061	0	0		0
7	M25.561	1	1	M25.5*	0		M20.*	M21.*	M26.6*	M65.2*	M71.4*	M75.3*	M79.6*	R26.*
8	m25.562	1	1	M25.5*	0		M20.*	M21.*	M26.6*	M65.2*	M71.4*	M75.3*	M79.6*	R26.*
9	m25.532	0	1	M25.5*	0
10	m54.12	1	1	M54.*	0		H45.41	M47.2*	M50.1*	M51.1*	M79.2*
11	m54.16	1	1	M54.*	0		F45.41	M47.2*	M50.1	M51.1*	M79.2*
12	m54.59	1	1	M54.*	0		F45.41	M51.2*	M54.4*	S39.012
13
14		1 indicates duplicates, 0 implies no duplicate
Sheet4

Cell Formulas
Range	Formula
B2:B12	B2	=IF(ISNUMBER(MATCH(A2,Source!$E$2:$AE$2,0)),1,0)
C2:C12	C2	=SUM(IF(ISNUMBER(MATCH($G$2:$AE$12,TOROW(A2),0)),1,0))
D2:D12	D2	=FILTER(TOROW($G$2:$AE$12),TOROW(IF(ISNUMBER(MATCH($G$2:$AE$12,A2,0)),1,0))<>0,"")
E2:E12	E2	=SUM(IF(ISNUMBER(MATCH(G2:AE2,D2:D12,0)),1,0))*IF(G2="",0,1)
G2:H2,G12:J12,G10:K11,G7:N8,G6,G9,G5:H5,G4:I4,G3:AE3	G2	=LET(a,TOROW(XLOOKUP(A2,Source!$E$2:$AE$2,Source!$E$3:$AE$34,0)),FILTER(a,a<>0,""))
Dynamic array formulas.