• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find value which has appeared for the maximum time in the data

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have a data, in column A I have names, I want to find out which name has appeared for the maximum time in the data.

I want a formula which will give me this result.

I have also attached a file for reference.

Can anyone help me in this please.

P.S. - I think we are not allowed to upload Excel Binary format file, this is not a complaint, just wanted to share this with the forum.
 

Attachments

Hi Xiq, thanks a lot for the help, helper column is good.
But I was thinking, wheather there is any such formula which will give me results without helper column.

I can hide the helper column, that is a soultion however I want to present this data to someone else, so if we can get the result without helper that would have been great.

Have a nice day !
 
Code: 
=INDEX($A$2:$A$9,MATCH(MAX(COUNTIF($A$2:$A$9,$A$2:$A$9)),COUNTIF($A$2:$A$9,$A$2:$A$9),0))
place this in B2 and ctrl + shift + enter.

I'm sure someone else can give a cleaner solution though :)
 
Hi Xiq, thanks a lot for the help.

This will definitely work.

I was trying something similar but forgot to convert it in array :(

Thanks once again, have a nice day ahead. :)
 
Narayan Sir, thanks a lot for the help. This is working. I checked it.

I will recheck it & will let you know if I face any problems.

I have also attached a file with new formula.

P.S. - This formula is given by Xiq.
 

Attachments

Hi Deb, thanks a lot for the help. I am using this formula only, given by Xiq a while ago

I do not know how the "=INDEX($A$2:$A$9,LOOKUP(9^9,COUNTIF($A$2:$A$9,$A$2:$A$9)))" works.
This is also given by Xiq, so I will stick to earlier one, have a nice day ahead :)
 
Hi Sachin ,

Try the formula with the following set of data :

bb
cc
aa
aa
cc
aa
cc
cc

It stands to reason that LOOKUP gives the maximum value ; using INDEX with this is not likely to give the correct result.

Narayan
 
Yes Sir, the result of "=INDEX($A$2:$A$9,LOOKUP(9^9,COUNTIF($A$2:$A$9,$A$2:$A$9)))" is aa but it should be cc.

This result is achieved by "=INDEX($A$2:$A$9,MATCH(MAX(COUNTIF($A$2:$A$9,$A$2:$A$9)),COUNTIF($A$2:$A$9,$A$2:$A$9),0))" (array).

Narayan Sir thanks a lot for helping me on this.
 
Hi Xiq, no problem at all, you are right, you had deleted the post, but I copied the formula from email alerts o_O

Thanks for the help.
 
You must log in or register to reply here.
Back
Top