Finding Most Common String, then 2nd Most Common String, etc, in a Column

[nj167252]

Here is my problem,

I am trying to calculate the most common string, the 2nd most common string, up to the 10th most common. Here is some of the data:
FVHA
FVHA
FVFA
FVMV
FVBU
FVHA
FVHA
FVFA
FVBU
and many more. Here is an example of the formula for finding the most common:
{=IFERROR(INDEX(Feb!$H$3:$H$30,MODE(MATCH(Feb!$H$3:$H$30,Feb!$H$3:$H$30,0))), "")}
This formula works great, except I want it to ignore blank cells.
Here is an example of the formula for finding the 2nd most common:

{=IFERROR(INDEX(Feb!$H$3:$H$198,MODE(IF(COUNTIF(I$5:I6,Feb!$H$3:$H$198)=0,MATCH(Feb!$H$3:$H$198,Feb!$H$3:$H$198,0)))),"")}
Again this works great but I would like to ignore blank cells.

If anyone could help me modify these formulas so I can ignore any blank cells in the column, I would be so thankful.

Regards!

 

[Marcelo Branco]

Maybe

Most common
=IFERROR(INDEX(Feb!$H$3:$H$30,MODE(IF(Feb!H3:H30<>"",MATCH(Feb!$H$3:$H$30,Feb!$H$3:$H$30,0)))), "")
Ctrl+Shift+Enter

M.

 

[oldbrewer]

text

FVHA

FVHA

FVFA

FVMV

FVBU

FVHA

Count of text

FVHA

text

Total

FVFA

FVBU

8

a pivot table lists all unique strings

FVBU

FVFA

7

FVFA

FVHA

10

FVMV

FVMV

4

FVBU

Grand Total

29

FVHA

FVHA

FVFA

FVBU

pulling out the numbers automatically

using large function

using offset match

FVMV

FVBU

1

8

10

FVHA

FVHA

2

7

8

FVBU

FVHA

3

10

7

FVFA

FVFA

4

4

4

FVMV

FVBU

5

FVFA

6

FVMV

7

FVBU

8

FVHA

9

FVHA

10

FVFA

11

FVBU

12

13

14

15

16

tidy up as you want

all these helper tables can be hidden…

<colgroup><col span="4"><col><col><col span="13"></colgroup><tbody>
</tbody>

 

[oldbrewer]

if any blanks present pivot table will catalogue them and formula can ignore them (or just turn off blanks in pivot table

 

[Marcelo Branco]

To list the most common, 2nd most common etc in another sheet try

	A​
1​	List​
2​	FVHA​
3​	FVFA​
4​	FVBU​
5​

Array formula in A2 copied down
=IFERROR(INDEX(Feb!$H$3:$H$30,MODE(IF(ISNA(MATCH(Feb!$H$3:$H$30,A$1:A1,0)),IF(Feb!$H$3:$H$30<>"",MATCH(Feb!$H$3:$H$30,Feb!$H$3:$H$30,0))))), "")
Ctrl+Shift+Enter

M.