Maximum Number (Count) of Occurrence

[Naresh Yadav]

Respected All,

I have a data with 3 Row and 5 column having the name of different people
I need a formula that can allow me to get the name of the people who is the maximum Number (Count) of Occurrence
kindly file the attached file with Desired Result...
thanks in advance..

 

[Asheesh]

Try the below


INDIRECT(TEXT(MAX(IF(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3)),ROW(A1:E3)*10^5+COLUMN(A1:E3))),"R0C00000"),)


It may not be as complicated as I am thinking…but yes, here is my shot…

To be acknowledged with Ctrl + Shift + Enter

 

[XOR LX]

Hi.

You don't say what the return should be if more than one person share the highest frequency.

That withstanding, this array formula** is one possible solution:

=INDIRECT(TEXT(MIN(IF(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3)),10^5*ROW(A1:E3)+COLUMN(A1:E3))),"R0C00000"),0)

Note that this set-up will require a small modification if null strings ("") are a possibility within the range.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[XOR LX]

@Asheesh

Snap!

 

[Asheesh]

@XOR LX hardly any difference...

 

[bines53]

Hello friends,

A little cumbersome, but it is also possible that,

=INDEX(A1:E3,AGGREGATE(15,6,ROW(A1:E3)/(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3))),1),AGGREGATE(15,6,COLUMN(A1:E3)/(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3))),1))

with Ctrl + Shift + Enter

David

 

[Naresh Yadav]

Respected Asheesh Sir,

thanks to giving your time to look and short out my Problem
I have got my desired result in your given array formula with one more query
while evaluating the formula I got "INDIRECT(TEXT(300003,"R0C00000"),) at the end of the formula

That will be a great help if you could kindly elaborate to me..

 

[Naresh Yadav]

Thank you so much "banes53" Sir for your reply..

 

[XOR LX]

@David

You should perhaps check your solution when more than one name shares the highest frequency.

If this is the case then the solutions given by Asheesh and myself will select one of those names; however, your formula may give an erroneous result in such cases.

Of course, the OP did not clarify if this was a possibility, nor even what the result should be in such cases. However, I thought it was worth pointing out.

Regards

 

[bines53]

Hi XOR LX ,

You're right !http://chandoo.org/forum/members/xor-lx.19535/

 

[John Jairo V]

Hi to all!

Another option is with Power Query. See the attach video and file to see how it works. Blessings!

 

[bines53]

Hello friends,


This time it's accurate, but more cumbersome,

=INDEX(A1:E3,AGGREGATE(15,6,ROW(A1:E3)/(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3))),1),AGGREGATE(15,6,COLUMN(A1:E3)/(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3))/(ROW(E1:E3)=AGGREGATE(15,6,ROW(A1:E3)/(COUNTIF(A1:E3,A1:E3)=MAX(COUNTIF(A1:E3,A1:E3))),1))),1))

with Ctrl + Shift + Enter

David

 

[Asheesh]

Ok, let me explain, It is R1C1 Referencing style that has been used here

Our construct currently looks like

INDIRECT(TEXT(300003,"R0C00000"),)

First lets look into the text part of the construct i.e.

Text(300003,"R0C00000") equals to R3C00003

Upon further evaluation resolves to R3C3 i.e Row 3 and Column 3.

Then finally wrapping it in the INDIRECT function i.e. INDIRECT(R3C3,) returns the value stored in the cell located at the intersection of Row 3 & Column 3.

Edit:
Go through the link below

http://www.techonthenet.com/excel/formulas/indirect.php

 

[Naresh Yadav]

Thanks and sorry for delay in replying Asheesh Sir..
thanks again bine sir..