Finding Top Ten repeated entries in list

[pstefens]

Hi, I'd like to find out the top ten most repeated entries in my list o
contract numbers. The MODE function, I've found, gives me the mos
repeated contract number from the list, but how do I find out whic
contract is repeated next-most frequently, and so on, down to #10

 

[Bernie Deitrick]

p,

If your list is in column A, with a heading in cell A1, then:

In cell B2, use the formula
=IF(COUNTIF($A$1:A2,A2)=1,COUNTIF(A:A,A2),"")

In cell C2, use the formula
=IF(B2<>"",IF(RANK(B2,B:B)<=10,RANK(B2,B:B),""),"")

and copy down to match your list in column A.

Then autofilter columns A:C, and filter column C to show non-blanks.

HTH,
Bernie
MS Excel MVP

 

[Aladin Akyurek]

What follows uses a small sample with Top N set to 5 (to be set to 10 in
your actual situation).

Suppose that A1:A30 houses the sample:

{"Contract#";"CON23";"CON22";"CON14";"CON20";"CON21";"CON20";
"CON25";"CON30";"CON23";"CON13";"CON15";
"CON23";"CON20";"CON15";"CON21";"CON15";
"CON30";"CON26";"CON21";"CON21";"CON26";
"CON25";"CON14";"CON26";"CON20";"CON23";
"CON10";"CON25";"CON10"}

In B2 enter & copy down:

=IF((A2<>"")*ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF($A$2:$A$30,A2),"")

In C2 enter & copy down:

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

In E2 enter: 5 (i.e., Top 5)

In E3 control+shift+enter (that is, type the formula which you confirm with
control+shift+enter instead of just with enter)...

=MAX(IF(INDEX(B2:B30,MATCH(E2,C2:C30,0))=B2:B30,C2:C30))-E2

In E5 enter & copy down:

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

The results area, with appropriate labels added, will now look like this:

{"Top";5;1;"Top
Contracts";"CON23";"CON20";"CON21";"CON25";"CON15";"CON26";"";""}

The behavior of the foregoing formula approach can also be obtained with
Pivot Tables, Advanced Filter with computed criteria, and AutoFilter.

 

[Debra Dalgleish]

Another option is to create a pivot table from the data. There are
instructions and links here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Add contract number to the row area, and also add contract number to the
data area, where it should become 'Count of Contract number'. (If it
shows 'Sum of Contract Number', double-click it, and choose to summarize
by Sum.

In the PivotTable, right-click on the 'Contract Number' button in the
row area. Choose Field Settings, and click the Advanced button.
Turn 'Top 10 AutoShow' on, and set the number to 10.