Top Values Count and duplicates

futaku

New Member
Joined
Mar 13, 2018
Messages
1
<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding:0px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="width: 65"]
<!--StartFragment--> <colgroup><col width="65" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]Hey guys, I'm new with excel esp in analysing data, sorry if this question is too basic for anyone that understand

So I've got this array of datas to work with, I need to find the top 5 values based on the the count of their names:

Hart[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Crux[/TD]
[/TR]
[TR]
[TD]Manuel[/TD]
[/TR]
[TR]
[TD]Bryan[/TD]
[/TR]
[TR]
[TD]Bryan[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Crux[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Manuel[/TD]
[/TR]
[TR]
[TD]Ziko[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

Initially I use the countif to get the count number for each name (Adam=5, John=3, Crux=2, Manuel=2, Bryan=2, Hart=1, Ziko=1) and then I use Large function to get the top values, but because there are duplicates in two different columns (the names and the "2"s), I can't properly do that.

I did use pivot table and sort, and got the result easily (Adam - John - Crux - Manuel - Bryan), but I do need the manual formulas as well to complete.

anyone can help me please? thank you
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I usually cheat this. I keep the Pivot Table and then allow the GETPIVOTDATA formula option when referencing a PivotTable cell.
If you want a straight formula without any use of PivotTable, you are probably closer than you think.
However, "but because there are duplicates in two different columns (the names and the "2"s)" will need to better explained. Your OP appears to leave out a clear explanation of this issue.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top