Looking for excel function that returns the most frequent text result in column J, only if the workplan category (column I), matches the column A.

[kennysmith1]

ex. "Business Office" (A2), the result in B2 would display "Omitted charges" as it is the most frequent text in column J for that workplan category. (Omitted charges 4x), (Data entry error 2x).
I have been trying to use index/match/mode functions with no success, as well as getpivot data functions.

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Workplan category	Result			Analyst	Jon			Workplan Category	RootCause
2	Business Office	Omitted Charges							Business Office	Data Entry Error
3	Capsule Endoscopy	#N/A							Business Office	Omitted Charges
4	Declot Access Device	Omitted Charges							Business Office	Omitted Charges
5	ED POCT Ultrasound	Modifier Missing or Invalid							Business Office	Omitted Charges
6	Epic WQ - Marker	#N/A							Business Office	Data Entry Error
7	Epic WQ - CAS Multiple Units	#N/A							Business Office	Omitted Charges
8									Capsule Endoscopy	Omitted Charges
9									Declot Access Device	Data Entry Error
10									Declot Access Device	Data Entry Error
11									Declot Access Device	Administration Type Change
12									Declot Access Device	Omitted Charges
13									Declot Access Device	Administration Type Change
14									Declot Access Device	Omitted Charges
15									Declot Access Device	Omitted Charges
16									ED POCT Ultrasound	Omitted Charges
17									ED POCT Ultrasound	Omitted Charges
18									ED POCT Ultrasound	Omitted Charges
19									ED POCT Ultrasound	Omitted Charges
20									ED POCT Ultrasound	Omitted Charges
21									ED POCT Ultrasound	Omitted Charges
22									ED POCT Ultrasound	Omitted Charges
23									ED POCT Ultrasound	Modifier Missing or Invalid
24									ED POCT Ultrasound	Modifier Missing or Invalid
25									ED POCT Ultrasound	Modifier Missing or Invalid
26									ED POCT Ultrasound	Modifier Missing or Invalid
27									ED POCT Ultrasound	Modifier Missing or Invalid
28									ED POCT Ultrasound	Modifier Missing or Invalid
29									ED POCT Ultrasound	Modifier Missing or Invalid
30									ED POCT Ultrasound	Modifier Missing or Invalid
31									ED POCT Ultrasound	Modifier Missing or Invalid
32									ED POCT Ultrasound	Modifier Missing or Invalid
Sheet7

Cell Formulas
Range	Formula
B2:B7	B2	=INDEX($J$2:$J$32,MODE(IF($I$2:$I$32=A2,MATCH($J$2:$J$32,$J$2:$J$32,0))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kennysmith1]

thanks Eric this is awesome!!! is it possible for the formula to work for the entire column J?
Ex. there are more results past row 32 and each month the file will change (~10,000 rows, etc) so wondering if there is a formula that will capture the entire column. i tried this and unfortunately if i update that J2:J32 to J:J this will read all the blank values in the column for the mode.

 

[kennysmith1]

i also noticed that i may have to include an IFERROR portion for those results where the MODE formula returns an error as there is only one result?

 

[Eric W]

In theory, you could use whole column references, but I wouldn't recommend it. Even with 10,000 rows, that's less than 1% of a column, and checking the entire column would slow down the calculations to a noticeable extent. I'd recommend creating a formula to find the last row, then using that in the main formula. Like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Workplan category	Result			Analyst	Jon			Workplan Category	RootCause		LastRow
2	Business Office	Omitted Charges							Business Office	Data Entry Error		32
3	Capsule Endoscopy	Omitted Charges							Business Office	Omitted Charges
4	Declot Access Device	Data Entry Error							Business Office	Omitted Charges
5	ED POCT Ultrasound	Administration Type Change							Business Office	Omitted Charges
6	Epic WQ - Marker	#N/A							Business Office	Data Entry Error
7	Epic WQ - CAS Multiple Units	#N/A							Business Office	Omitted Charges
8									Capsule Endoscopy	Omitted Charges
9									Declot Access Device	Data Entry Error
10									Declot Access Device	Data Entry Error
11									Declot Access Device	Administration Type Change
12									Declot Access Device	Omitted Charges
13									Declot Access Device	Administration Type Change
14									Declot Access Device	Omitted Charges
15									Declot Access Device	Omitted Charges
16									ED POCT Ultrasound	Omitted Charges
17									ED POCT Ultrasound	Omitted Charges
18									ED POCT Ultrasound	Omitted Charges
19									ED POCT Ultrasound	Omitted Charges
20									ED POCT Ultrasound	Omitted Charges
21									ED POCT Ultrasound	Omitted Charges
22									ED POCT Ultrasound	Omitted Charges
23									ED POCT Ultrasound	Modifier Missing or Invalid
24									ED POCT Ultrasound	Modifier Missing or Invalid
25									ED POCT Ultrasound	Modifier Missing or Invalid
26									ED POCT Ultrasound	Modifier Missing or Invalid
27									ED POCT Ultrasound	Modifier Missing or Invalid
28									ED POCT Ultrasound	Modifier Missing or Invalid
29									ED POCT Ultrasound	Modifier Missing or Invalid
30									ED POCT Ultrasound	Modifier Missing or Invalid
31									ED POCT Ultrasound	Modifier Missing or Invalid
32									ED POCT Ultrasound	Modifier Missing or Invalid
Sheet7

Cell Formulas
Range	Formula
L2	L2	=LOOKUP(2,1/(J:J<>""),ROW(J:J))
B2:B7	B2	=IFERROR(INDEX($J$2:INDEX($J:$J,LastRow),MODE(IF($I$2:INDEX($I:$I,LastRow)=A2,MATCH($J$2:INDEX($J:$J,LastRow),$J$2:INDEX($J:$J,LastRow,0))))),VLOOKUP(A2,$I$2:INDEX($J:$J,LastRow),2,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
LastRow	=Sheet7!$L$2	B2:B7

I used a Named Range, but there are other ways to do this. I added the IFERROR in case of a single matching row, but you'll still get the #N/A error for no matches.

 

[kennysmith1]

thank you very much Eric, i think this is getting very close to what I am looking for. Im trying the formula without the IFERROR function, and am now getting an error for some even though there is clearly a MODE. Ex. 'LI OBOP Sample - IV Therapy' should return "IV Start/Stop time missing", but instead returns an error #N/A.
'LI OBOP Sample - Laboratory' correctly returns "omitted charges", not sure why this is? Note i transposed the formula to use the same columns in different locations, and also created a separate key to vlookup.

=INDEX('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),MODE(IF('Raw Data'!$AN$2:INDEX('Raw Data'!$AN:$AN,Key!$D$4)=Sheet32!C22,MATCH('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),'Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4,0)))))

 

[Eric W]

Looking at your data, I don't see anything that would cause your issue. My best guess would be that the keys don't match. A trailing space that you can't see, or some undisplayable character. In order to troubleshoot this, are you able to use the xl2bb add-in? This will allow you to copy your exact data (like I did in posts 2 and 5) so that I can copy it directly to my Excel and work with it. When I look at a picture like you've shown, it's easy to miss something. Look at the link in my signature, or in the reply box. It's easy to download, install, and use.

 

[kennysmith1]

use this for Feb QP.xlsx
	C	D	E	F	G	H	I	J
1	A+B	Net Adjusted amount	Count	Added Charges	Removed Charges	Root Cause	h3 without iferror	vlookup issue, taking the first value not the max??
22	LI OBOP Sample - IV Therapy	$ 6,816.41	14	$ 15,182.46	$ 8,366.05	Omitted Charges	#N/A	Omitted Charges
23	LI OBOP Sample - Laboratory	$ 459.30	10	$ 551.16	$ 91.86	Omitted Charges	Omitted Charges	Omitted Charges
24	LI OBOP Sample - Other Diagnostic Services	$ (23,911.70)	37	$ 71,735.10	$ 95,646.80	Modifier Missing or Invalid	Modifier Missing or Invalid	Modifier Missing or Invalid
25	LI OBOP Sample - Radiology	$ (98.90)	2	$ 414.59	$ 513.49	Bundled Procedure	Bundled Procedure	Incorrect Order Selected
26	LI OBOP Sample - Treatment/Observation Room	$ (30,345.00)	28	$ 49,436.00	$ 79,781.00	Omitted Charges	#N/A	Omitted Charges
27	LI OBOP Sample - Ultrasound	$ (870.72)	18	$ 8,693.96	$ 9,564.68	No MD Order	No MD Order	Incorrect Order Selected
Sheet32

Cell Formulas
Range	Formula
C22:C27	C22	=CONCATENATE(A22," - ",B22)
D22:D27	D22	=SUMIFS('Raw Data'!AD:AD,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22)
E22:E27	E22	=COUNTIFS('Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22)
F22:F27	F22	=SUMIFS('Raw Data'!AG:AG,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22,'Raw Data'!AB:AB,"Undercharge")
G22:G27	G22	=SUMIFS('Raw Data'!AG:AG,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22,'Raw Data'!AB:AB,"Overcharge")
H22:H27	H22	=IFERROR(INDEX('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),MODE(IF('Raw Data'!$AN$2:INDEX('Raw Data'!$AN:$AN,Key!$D$4)=Sheet32!C22,MATCH('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),'Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4,0))))),VLOOKUP(Sheet32!C22,'Raw Data'!$AN$2:INDEX('Raw Data'!$AP:$AP,Key!$D$4),3,0))
I22:I27	I22	=INDEX('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),MODE(IF('Raw Data'!$AN$2:INDEX('Raw Data'!$AN:$AN,Key!$D$4)=Sheet32!C22,MATCH('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),'Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4,0)))))
J22:J27	J22	=VLOOKUP(Sheet32!C22,'Raw Data'!$AN$2:INDEX('Raw Data'!$AP:$AP,Key!$D$4),3,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
RootCause	='Raw Data'!$AE$2:$AE$1048576	H22:I27

 

[kennysmith1]

use this for Feb QP.xlsx
	AN	AO	AP
4568	LI OBOP Sample - IV Therapy	LI OBOP Sample	Omitted Charges
4569	LI OBOP Sample - IV Therapy	LI OBOP Sample	Administration Type Change
4570	LI OBOP Sample - IV Therapy	LI OBOP Sample	Omitted Charges
4571	LI OBOP Sample - IV Therapy	LI OBOP Sample	Omitted Charges
4572	LI OBOP Sample - IV Therapy	LI OBOP Sample	Modifier Missing or Invalid
4573	LI OBOP Sample - IV Therapy	LI OBOP Sample	Omitted Charges
4574	LI OBOP Sample - IV Therapy	LI OBOP Sample	IV Start/Stop Time Missing
4575	LI OBOP Sample - IV Therapy	LI OBOP Sample	IV Start/Stop Time Missing
4576	LI OBOP Sample - IV Therapy	LI OBOP Sample	Administration Type Change
4577	LI OBOP Sample - IV Therapy	LI OBOP Sample	Modifier Missing or Invalid
4578	LI OBOP Sample - IV Therapy	LI OBOP Sample	IV Start/Stop Time Missing
4579	LI OBOP Sample - IV Therapy	LI OBOP Sample	IV Start/Stop Time Missing
4580	LI OBOP Sample - IV Therapy	LI OBOP Sample	IV Start/Stop Time Missing
4581	LI OBOP Sample - IV Therapy	LI OBOP Sample	IV Start/Stop Time Missing
Raw Data

Cell Formulas
Range	Formula
AN4568:AN4581	AN4568	=CONCATENATE(AJ4568," - ",Y4568)
AO4568:AO4581	AO4568	=AJ4568
AP4568:AP4581	AP4568	=AE4568

 

[kennysmith1]

Thanks was able to install let me know if the formatting is ok or if any data is missing, first time using x2lbb