Return 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]

It looks like you have some of your column references mixed up. Here's the formula as you have it in post 8:

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)))))

Here's how it should be:

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

I also found an issue that could cause errors if you have any blank rows in your data. Let me know if that could happen.

 

[kennysmith1]

It looks like you have some of your column references mixed up. Here's the formula as you have it in post 8:

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)))))

Here's how it should be:

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

I also found an issue that could cause errors if you have any blank rows in your data. Let me know if that could happen.

Sorry i forgot to include a screenshot for column AE, (it is the same as column AP) so don't think that is the issue? if there is nothing else wrong with the formula, maybe i would have to add an IFERROR for those results im having issues with, then a second IFERROR to capture the results without a MODE?

 

[Eric W]

In that case, it appears that it's likely the blank rows issue. I can fix that with an IFERROR around the INDEX(MATCH section.

Book1
	C	D	E	F	G	H	I	J
22	LI OBOP Sample - IV Therapy	0	0	0	0	Omitted Charges	Omitted Charges	Omitted Charges
Sheet32

Cell Formulas
Range	Formula
D22	D22	=SUMIFS('Raw Data'!AD:AD,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22)
E22	E22	=COUNTIFS('Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22)
F22	F22	=SUMIFS('Raw Data'!AG:AG,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22,'Raw Data'!AB:AB,"Undercharge")
G22	G22	=SUMIFS('Raw Data'!AG:AG,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22,'Raw Data'!AB:AB,"Overcharge")
H22	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	I22	=INDEX('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),MODE(IFERROR(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	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.

 

[kennysmith1]

In that case, it appears that it's likely the blank rows issue. I can fix that with an IFERROR around the INDEX(MATCH section.

Book1
	C	D	E	F	G	H	I	J
22	LI OBOP Sample - IV Therapy	0	0	0	0	Omitted Charges	Omitted Charges	Omitted Charges
Sheet32

Cell Formulas
Range	Formula
D22	D22	=SUMIFS('Raw Data'!AD:AD,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22)
E22	E22	=COUNTIFS('Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22)
F22	F22	=SUMIFS('Raw Data'!AG:AG,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22,'Raw Data'!AB:AB,"Undercharge")
G22	G22	=SUMIFS('Raw Data'!AG:AG,'Raw Data'!AJ:AJ,Sheet32!A22,'Raw Data'!Y:Y,Sheet32!B22,'Raw Data'!AB:AB,"Overcharge")
H22	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	I22	=INDEX('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),MODE(IFERROR(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	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.

I think that vlookup is picking up the first value, ex for LI OBOP Sample - IV therapy, ideally this would be IV/Start/stop time missing, however it is picking up the "Omitted cHARGES"

 

[Eric W]

This is driving me crazy. I think I may have found a bug in Excel. I hate to say that, because whenever someone else has said it, it usually turned out that they didn't understand what was supposed to happen. Even so, what I'm seeing when I test is inexplicable to me. In short, when I used the MATCH(A2:INDEX(),A2:INDEX,0) structure, I get odd results I can't explain. Even when I run the Evaluate Formula tool, it resolves to the proper ranges, but gives out nonsensical results. When I try MATCH(A2:A5000,A2:A5000,0), everything works as expected.

So in this case, I'm going to have to backtrack on my comments about using whole column references. Try this:

=INDEX('Raw Data'!$AE$2:$AE$5000,MODE(IFERROR(IF('Raw Data'!$AN$2:$AN$5000=Sheet32!C22,MATCH('Raw Data'!$AE$2:$AE$5000,'Raw Data'!$AE$2:$AE$5000,0)*{1,1}),"")))

I used a row of 5000 for the end. You can use a higher value if you deem it necessary. You can use whole column references just by removing the rows on the AE and AN ranges, but it lags noticeably with just one formula on my sheet, and will probably lag a lot more depending on how many you have.

Sorry about the confusion! This surprised me too.

 

[Eric W]

Argh! I KNEW it wasn't a bug. I had a parameter in the wrong place. It took me a while to figure it out, but here's the proper formula, still using the value in Key!D4 for the bottom row:

=INDEX('Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),MODE(IFERROR(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!$D4),'Raw Data'!$AE$2:INDEX('Raw Data'!$AE:$AE,Key!$D$4),0)*{1,1}),"")))

 

[kennysmith1]

U ARE THE GOAT! thanks very much. Think this is working but want to test it more but excel is giving me a problem not sure why.
I copied the formula into row 2 and edited the portion for Sheet32!C2 instead of C22, the result is perfect, "Data entry Error" as expected. Then when I go to double click/fill down the column all the results are "data entry error" (even though the formula automatically updates, Sheet!32C3, C4, C5... etc.) Then when I copy and paste the formula in manually, ex. C22 it works! Do you know why this could be? My file is pretty large and has been running slow but this one stumped me.

 

[kennysmith1]

U ARE THE GOAT! thanks very much. Think this is working but want to test it more but excel is giving me a problem not sure why.
I copied the formula into row 2 and edited the portion for Sheet32!C2 instead of C22, the result is perfect, "Data entry Error" as expected. Then when I go to double click/fill down the column all the results are "data entry error" (even though the formula automatically updates, Sheet!32C3, C4, C5... etc.) Then when I copy and paste the formula in manually, ex. C22 it works! Do you know why this could be? My file is pretty large and has been running slow but this one stumped me.

Eventually the entire column updates after being stuck at 'calculating(4 processor(s)) 1%' for like 10 minutes haha. just shows how complex the formula is!! thanks again.

 

[kennysmith1]

Update: After the column appeared to update automatically after 10 minutes I realized it updated with the incorrect result. However, manually copying your formula and changing the formula for the correct Key (C2, C3, C4 etc.) still works. Weird!

Ex.

Key: Business Office - Minor Surgery/Procedures showed up as Modifier missing/invalid (even though there are no results for this). However manually copying/pasting the formula returns "Omitted Charges" as expected (Correct!).

 

[Eric W]

That does sound weird. And you are using Control+Shift+Enter? I can't explain it. But it sounds like you've got it working OK, let me know if you need something else.