• 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.

Formula to extract Name that appears most frequently in a List

V

vicmoment

Member
I have a list wth player names and their clubs. I have a dropdown in another sheet from which I select the club whose records i want to see. I want to be able to select a club and with a formula to know whose name appears most frequently in the list.


See sample workbook in


https://docs.google.com/spreadsheet/ccckey=0An2orugVlNhDdEV4R1owbFk4Z1RSY19KbWd3TENSRHc#gid=0


Thanks


Victor
 
Hi Victor ,


I get the following message when trying to access your file :

[pre]
Code: 
Sorry, the page (or document) you have requested does not exist.

Please check the address and try again.
[/pre]
Narayan
 
Hi Narayank,


Pls try this

https://docs.google.com/spreadsheet/ccc?key=0An2orugVlNhDdEV4R1owbFk4Z1RSY19KbWd3TENSRHc&pli=1#gid=0


What I need is to rreturn the player name that appears most frequently for each club
 
Hi Victor ,


Before posting your link here , if you give access rights to all those who have the link , then anyone can access your file from this forum ; otherwise , whoever wishes to access your file has to request for permission.


Narayan
 
Hello Victor,


I cannot acces your files (blocked by proxy at work), but I assume you will need a helper column in your reference data sheet. This column will count the occurrence the pair club/player already appeared above in the list. Let me illustrated this :

[pre]
Code: 
.|  A            B             C           D
-+-------------------------------------------------------------------
1|  Club         Player        Concat      Occurence
2|  Club1        Player1       =A2&B2      =COUNTIF($C$2:C2; C2)
3|  Club1        Player2       =A3&B3      =COUNTIF($C$2:C3; C3)
4|  Club1        Player2       ...         ...
5|  Club1        Player3
6|  Club2        Player1
7|  Club2        Player4
8|  Club2        Player4

Add a cell with Data Validation List containing the list of clubs (in G2 for illustration purpose)

Add another cell (H2 )containing the following formula :


=OFFSET( $B$1, MATCH( MAX( OFFSET( $D$1, MATCH( G2, A2:A9,0),0, COUNTIF( A2:A9, G2),1)),
OFFSET( $D$1, MATCH( G2, A2:A9,0),0, COUNTIF(A2:A9,G2),1),0 ),0)
[/pre]
Selecting Club1 in the list of club will return Player2


Hope this help


Cyril
 
Narayank/Cyril


I am sorry about the permission thingy, I have modified the access.


https://docs.google.com/spreadsheet/ccc?key=0An2orugVlNhDdEV4R1owbFk4Z1RSY19KbWd3TENSRHc&pli=1#gid=0


Sorry for the inconvenience
 
Hi vicmoment,


What in case there is tie?? Your data has got a few cases in which there are two or three players that occur most frequently for a club..


Regards,

Faseeh
 
@ SirJB7, I was trying with this file but stuck at this stage so i asked gentlemen could he reply the query. BTW u will be next excel ninja after 59 posts! Congrats Sir :)


Faseeh
 
Faseeh,


Thanks for your observation.


Iknow its not full proof but I would like to at least be able to return one of them, i probably would then think of extending it to accomodate ties
 
Hi Victor ,


Check out the workbook at the following link :


https://docs.google.com/open?id=0B0KMpuzr3MTVQko5Ym42UmtSQWVLeGVOQkFJSHdydw


Narayan
 
HI Narayan,


You are simply amazing.


It works except when there's a tie which is good itself


I have not been able to wrap my head around the magic going on there so if you could break the formula down a bit, that would be fine.
 
Hi Victor ,


It's Excel which is doing the magic !


1. Just to make things easy to understand , three ranges have been named - List_of_players , List_of_players_clubs , and List_of_clubs ; the first two refer to the raw data columns , column A and column B ; the last one refers to the unique list of clubs , which is derived from the list of players' clubs in column J , by using the following formula :


=OFFSET(Sheet1!$J$2,0,0,SUMPRODUCT(1/COUNTIF(List_of_players_clubs, List_of_players_clubs)))


2. The data validation drop-down is straightforward , since it refers to the named range List_of_clubs for the list.


3. The most frequent player within the selected club is by the formula :


=INDEX(List_of_players,MODE(IF((--(List_of_players_clubs=D2)*MATCH(List_of_players,List_of_players,0))>0,--(List_of_players_clubs=D2)*MATCH(List_of_players,List_of_players,0))))


In this formula , the operative part is :


MODE(--(List_of_players_clubs=D2)*MATCH(List_of_players,List_of_players,0))


Only , this creates an array of values which satisfies the criterion selecting those clubs which match the club selected in the drop-down ; since this has 0 in those entries which do not satisfy the criterion , the IF statement excludes those entries , and returns the most frequently occurring non-zero value ; this value is just a value from 1 through 20 , 1 referring to the first entry in the lists , 2 referring to the second entry , and 20 referring to the 20th entry. Using this to index the list of players in column A returns the relevant player's name.


I will try to prepare a more detailed document in the coming week.


Narayan
 
You must log in or register to reply here.
Back
Top