How to find most and second most common entry from alpha list

Joah,

Attached is an example workbook based on the criteria you described. It contains both scenarios from your original post.

To get the nth most common without a numeric value, in cell D3 and copied down is this formula:

=INDEX($A$3:$A$8,MATCH(LARGE(INDEX(COUNTIF($A$3:$A$8,$A$3:$A$8),),1+SUMPRODUCT(COUNTIF($A$3:$A$8,$D$2:D2))),INDEX(COUNTIF($A$3:$A$8,$A$3:$A$8),),0))

To get the nth most common with a numeric value, in cell J3 and copied down is this formula:

=INDEX($F$3:$F$8,MATCH(LARGE(INDEX(SUMIF($F$3:$F$8,$F$3:$F$8,$G$3:$G$8),),1+SUMPRODUCT(COUNTIF($F$3:$F$8,$J$2:J2))),INDEX(SUMIF($F$3:$F$8,$F$3:$F$8,$G$3:$G$8),),0))

Joah,

I am unable to duplicate that issue. What is the list of items that is causing an incorrect result?

Joah, if you change values, be sure that calculation is set to Automatic. (Excel Options > Formulas)

Joah,

Ah, I see. Attached is version 2, with corrected formulas for both methods to prevent that when there are exactly the same number of an entry.

Method 1 (no values), in cell D3 and copied down:

=INDEX($A$3:$A$8,MATCH(1,INDEX((COUNTIF($A$3:$A$8,$A$3:$A$8)=LARGE(INDEX(COUNTIF($A$3:$A$8,$A$3:$A$8),),1+SUMPRODUCT(COUNTIF($A$3:$A$8,$D$2:D2))))*(COUNTIF($D$2:D2,$A$3:$A$8)=0),),0))

Method 2 (with values), in cell J3 and copied down:

=INDEX($F$3:$F$8,MATCH(1,INDEX((SUMIF($F$3:$F$8,$F$3:$F$8,$G$3:$G$8)=LARGE(INDEX(SUMIF($F$3:$F$8,$F$3:$F$8,$G$3:$G$8),),1+SUMPRODUCT(COUNTIF($F$3:$F$8,$J$2:J2))))*(COUNTIF($J$2:J2,$F$3:$F$8)=0),),0))

The reference to $d$2:d2 is there to advance to the next nth item in the list. It doesn't have to be a blank cell in D2, it could be a header, or any text you'd like. The reference grows as the formula is copied down. So in cell D4, that reference becomes $d$2:d3. Again, this is solely to advance to the next nth item.

Yes. So the formula in cell D3 would become:
=IFERROR(existing formula,D2)

Then copy down.

Alright, that range reference is used in this function: COUNTIF($A$3:$A$8,$D$2:D2)

The $ symbols create an abolute reference, so as the formula is copied from one cell to another, the absolute reference won't change. So as the formula is copied down, the reference becomes:
$D$2:D3
$D$2:D4
etc

In this case, it only gets copied down once.

What that function actually does, is count the number of occurrences within a cell range. The cell range is the first argument, $A$3:$A$8. What it's looking for so that it count the occurrences is the second argument, $D$2:D2.
D2 is blank, so CountIf returns a 0. That number is added to 1 (1+0=1) to produce the k reference for the Large() function which is called earlier.
When the formula is copied down, it counts all occurrences of both D2 and D3. D3 contains the formula's first result, so in this case it would be elf, and there are 3 occurrences of elf. So now it is 1+0+3=4. So now the Large function gets the 4th entry, which is fed to the rest of the function to produce the next result.

I'm not sure if that helps, but that's what that reference is there for, to advance to the next nth value ignoring duplicates.

That formula is correct, and is returning a 0 because you have 1 or more blank cells in C4:C28. Here's an updated formula using the ranges provided that will prevent the formula from looking at blank cells. This should go in C30 and get copied down:

=IFERROR(INDEX($C$4:$C$28,MATCH(1,INDEX((COUNTIF($C$4:$C$28,$C$4:$C$28)=LARGE(INDEX(COUNTIF($C$4:$C$28,$C$4:$C$28),),1+SUMPRODUCT(COUNTIF($C$4:$C$28,$C$29:C29))))*(COUNTIF($C$29:C29,$C$4:$C$28)=0)*($C$4:$C$28<>""),),0)),C29)

Consider A3 to down are entries,

D3 with CTRL+SHIFT+ENTER, then copy down.

=IFERROR(INDEX(A$3:A$1000,MODE(IF(ISNA(MATCH(A$3:A$1000,D$2:D2,0)),IF(A$3:A$1000<>"",MATCH(A$3:A$1000,A$3:A$1000,0))))),"")

Adjust the range.

This wouldn't return single entries