find most common name in a column

Try this one.
For Horizantal Data : Suppose your data column row from "A2" to "J2"

Formula:

Please Login or Register  to view this content.

For Vertical Data : Suppose your data column "A2" to "A10"

Formula:

Please Login or Register  to view this content.

I realized when using your coding is that I see that most common are empty rows.
How to define the coding so not filled will not recognized?
empty field or "unknown" should not be recognized.

Thanks so far

Use a helper column (B) to count the occurrences of each name in column A.
Assuming that the names start in A2 and go down the column enter this in B2 and fill down the length of the data

Formula:

Please Login or Register  to view this content.

Where you want the top 3 names enter this formula and fill down 3 rows total. If you fill down further, you will get the names in order of occurrences.

Formula:

Please Login or Register  to view this content.

Data Range

	A	B	C	D
1	Name	Count		Top 3
2	a	3.87783		e
3	a			t
4	a			r
5	t	4.235136
6	t
7	t
8	t
9	x	2.506083
10	x
11	e	6.84222
12	e
13	e
14	e
15	e
16	e
17	r	3.934245
18	r
19	r
20	s	2.069983
21	s

Originally Posted by newdoverman

=IFERROR(IF(COUNTIF($A$2:A2,A2)=1,COUNTIF(A:A,A2),"")+RAND(),"")

Although unlikely, it's still possible to get duplicates with the RAND() function.

For guaranteed unique numbers do something like this...

COUNTIF(...)-ROW()/1000000

Good idea Tony. Thanks for the input. Wouldn't this bias the tie-breaking in favour of the item higher up the list due to the increasing row number being divided by a constant resulting in a slightly overall lower value?

I guess there's "bias" no matter which method you use.

I was just looking at it from the perspective of generating guaranteed unique numbers.

There are other methods of generating a top N list which accounts for ties.

To incorporate the guaranteed "no duplicates" use this which is a combination of my original formula with Tony Valko's suggestion. If there is a duplicate, the first duplicate in the list gets chosen.
Enter in B2 and fill down

Formula:

Please Login or Register  to view this content.

The top 3 are then calculated by the same formula as in Msg#5

Formula:

Please Login or Register  to view this content.

Data Range

	A	B	C
1
2	b	1.999998	m
3	b		f
4	v	1.999996	t
5	v
6	f	3.999994
7	f
8	f
9	f
10	r	1.99999
11	r
12	t	3.999988
13	t
14	t
15	t
16	h	3.999984
17	h
18	h
19	h
20	m	5.99998
21	m
22	m
23	m
24	m
25	m
26	u	3.999974
27	u
28	u
29	u

To get the 5 largest values enter in I22 and fill down

Formula:

Please Login or Register  to view this content.

To get the corresponding text value, enter in G22 and fill down (enter with Ctrl + Shift + Enter)

Formula:

Please Login or Register  to view this content.

A better formula to use to get the text values associated with the top 5 values.
Enter in G22 and fill down

Formula:

Please Login or Register  to view this content.

Thank you for the feedback. I recommend that you use the "better" formula as it is not an array formula and is much more simple.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.