Need top 5 most frequent (non-numeric) values

[yobroham]

I am trying to create a top 5 list of the most frequent values from a range. I know how to use the MODE function to get the most frequent number in a range, but I want to find the most frequent text...as well as get the next 4 most frequent. For example...

My range would be a1:a16 and I need a formula to place into d2 for the most frequent non-numerical value in the range, d3 for the second most frequent and so on...


ANY help would be greatly appreciated, excel newbie here

 

[shg]

You could copy the values to another column, use Remove Duplicates to get a unique list, and then use a COUNTIF formula to count the occurrences of each.

 

[MikeWx]

If you are still looking for a formula solution, mikerickson has one at

Here is some sample data with his formula. Copy cell D2 down for the other four values. Ties on count are not in any particular order.

Excel Workbook
	A	B	C	D
1	Fred	*	*	*
2	Ralph	*	*	Fred
3	George	*	*	Susan
4	Susan	*	*	George
5	Tom	*	*	Jeff
6	Jeff	*	*	Tom
7	Fred	*	*	*
8	Tom	*	*	*
9	Susan	*	*	*
10	Fred	*	*	*
11	George	*	*	*
12	Susan	*	*	*
13	Fred	*	*	*
14	Jeff	*	*	*
15	George	*	*	*
16	Carol	*	*	*
Sheet1

Mike

 

[Aladin Akyurek]

I am trying to create a top 5 list of the most frequent values from a range. I know how to use the MODE function to get the most frequent number in a range, but I want to find the most frequent text...as well as get the next 4 most frequent. For example...

My range would be a1:a16 and I need a formula to place into d2 for the most frequent non-numerical value in the range, d3 for the second most frequent and so on...


ANY help would be greatly appreciated, excel newbie here

[TABLE="width: 261"]
<TBODY>[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Fred
[/TD]
[TD="class: xl66, width: 28, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 28, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl67, width: 104, bgcolor: transparent"]MOST FREQ
[/TD]
[TD="class: xl67, width: 78, bgcolor: transparent"]FREQ
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Ralph
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl69, bgcolor: #ffff99"]Fred
[/TD]
[TD="class: xl70, bgcolor: #ffff99, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]George
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl69, bgcolor: #ffff99"]George
[/TD]
[TD="class: xl70, bgcolor: #ffff99, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Susan
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #ffff99"]Susan
[/TD]
[TD="class: xl70, bgcolor: #ffff99, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Tom
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #ffff99"]Tom
[/TD]
[TD="class: xl70, bgcolor: #ffff99, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Jeff
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #ffff99"]Jeff
[/TD]
[TD="class: xl70, bgcolor: #ffff99, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Fred
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #ffff99"][/TD]
[TD="class: xl70, bgcolor: #ffff99"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Tom
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Susan
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Fred
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]George
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Susan
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Fred
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Jeff
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]George
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, width: 110, bgcolor: white"]Carol
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

C1: 5 (Top 5)

C2, control+shift+enter (CSE), not just enter:

=LARGE(FREQUENCY(IF($A$1:$A$16<>"",MATCH($A$1:$A$16,$A$1:$A$16,0)),
  ROW($A$1:$A$16)-ROW($A$1)+1),C1)

C3, CSE:

=SUM(IF(FREQUENCY(IF($A$1:$A$16<>"",MATCH($A$1:$A$16,$A$1:$A$16,0)),
  ROW($A$1:$A$16)-ROW($A$1)+1)>=C2,1))

D2, CSE and copy down:

=IF($E2="","",INDEX($A$1:$A$16,SMALL(IF(FREQUENCY(IF($A$1:$A$16<>"",
  MATCH($A$1:$A$16,$A$1:$A$16,0)),ROW($A$1:$A$16)-ROW($A$1)+1)=$E2,
  ROW($A$1:$A$16)-ROW($A$1)+1),COUNTIF($E$2:E2,E2))))

E2, CSE and copy down:

=IF(ROWS($E$2:E2)<=$C$3,LARGE(FREQUENCY(IF($A$1:$A$16<>"",
  MATCH($A$1:$A$16,$A$1:$A$16,0)),ROW($A$1:$A$16)-ROW($A$1)+1),
  ROWS($E$2:E2)),"")