show multiple occuring name in a column

  • Thread starter Thread starter gr8guy
  • Start date Start date
G

gr8guy

Hi,

I have a Column which has first names of people e.g:

Aju
Zaki
Aireen
Mumma
Fahad
Didi
Aju
Didi
Aireen
Mumma
Aju
Aju

In any one cell in a different column, i want to show the name occuring
multiple times in the Column, like in this case, the commonly occuring name
was "Aju".

How do i mk a formula which will calculate & display (not the count) only
the name occuring a many times in the column. It should be the maximum no
then other commonly occuring names. like e.g above column, "Aju" occured 3
times, Didi occured 2 times, Mumma occured 2 times, so "Aju" is the maximum
occuring name in the column.

2) What if, there r 2 names occuring EQUAL NUMBER of times in a column, what
do i do in such a case?

How do i prioritise a name so that, even if it is equally occuring as some
other name, it has been given more priority, so it will only reflect in the
cell. HOW DO I CREATE SUCH A FORMULA IN THE CELL?


Rgds,

Eijaz
 
gr8guy said:
I have a Column which has first names of people e.g:

Aju
Zaki
Aireen
Mumma
Fahad
Didi
Aju
Didi
Aireen
Mumma
Aju
Aju

In any one cell in a different column, i want to show the name occuring
multiple times in the Column, like in this case, the commonly occuring name
was "Aju".
Click to expand...
....

If your data above were in A1:A12, try the array formula

=INDEX(A1:A12,MATCH(MAX(COUNTIF(A1:A12,A1:A12)),COUNTIF(A1:A12,A1:A12),0))
2) What if, there r 2 names occuring EQUAL NUMBER of times in a column, what
do i do in such a case?
Click to expand...
....

You need to decide what to do. Either display all names occuring at least as
many times as all other names, or display one such name. In the latter case,
the single name picked would be arbitrary, so might as well use the topmost
one, in which case you could still use the array formula above.
 
Let A2:A13 house the names sample you provided.

In B1 enter: Count

In B2 enter & copy down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF($A$2:$A$13,A2))

In C1 enter: Rank

In C2 enter & copy down:

=IF(N(B2),RANK(B2,$B$2:$B$13)+COUNTIF($B$2:B2,B2)-1,"")

In E1 enter: 1 [ the Top N parameter, with N set to 1 ]

Type in E2

=MAX(IF(INDEX(B2:B13,MATCH(E1,C2:C13,0))=B2:B13,C2:C13))-E1

and confirm this formula with control+shift+enter instead of just with
enter.

In E3 enter & copy down:

=IF(ROW()-ROW($E$3)+1<=$E$1+$E$2,INDEX($A$2:$A$8,MATCH(ROW()-ROW($E$3)+1,$C$
2:$C$8,0)),"")

The results of interest will show up in the range that starts in E3.
 
Back
Top