[KINNEY1978]

I need help with an Excel formula.

In Worksheet #1 I have roughly 800 state abbreviations listed. The column is M.

I need to set a formula to pull the top five most occurring states and automatically place them in Worksheet #2 in cells A19:A23.

I thank you in advance for your assistance!

[Advertisements]

This by no means is an elegant solution; I'm sure there are better, less clunky methods.

I started with a five column table in a different sheet (I usually hide a sheet like this).

In column B ("State") is a unique list of your states, deduped and ordered alphabetically (i.e. AK, AL, AR, AZ, etc.)
Column C ("Count") is a countif that would be in c2 =COUNTIF(Sheet1!M:M,B2)
Column D ("Index") is descending integers (i.e. 50,49,48,47,...,1)
Column E ("Ranker") takes 100*column c + column b, i.e. in E2 = C2*100+D2.
Now you use a rank function in column A ("Rank") that in A2 would rank E2 descending, i.e. =Rank(E2,E:E,0)

Now column A gives you the order in which the states appear, and in case of a tie the index will rank the ties in alphabetical order with a unique rank number for each. For example, if 4,5, and 6th place were TX, AL, and FL with 16 counts each, it would fill the rank function with a 4 for AL, 5 for FL and 6 for TX because it ranks them alphabetically.

To fill your A19:A23 you would just need a vlookup function that would return the state for the rank you are looking for.

Hope that helps.

[bomtond]

This by no means is an elegant solution; I'm sure there are better, less clunky methods.

I started with a five column table in a different sheet (I usually hide a sheet like this).

In column B ("State") is a unique list of your states, deduped and ordered alphabetically (i.e. AK, AL, AR, AZ, etc.)
Column C ("Count") is a countif that would be in c2 =COUNTIF(Sheet1!M:M,B2)
Column D ("Index") is descending integers (i.e. 50,49,48,47,...,1)
Column E ("Ranker") takes 100*column c + column b, i.e. in E2 = C2*100+D2.
Now you use a rank function in column A ("Rank") that in A2 would rank E2 descending, i.e. =Rank(E2,E:E,0)

Now column A gives you the order in which the states appear, and in case of a tie the index will rank the ties in alphabetical order with a unique rank number for each. For example, if 4,5, and 6th place were TX, AL, and FL with 16 counts each, it would fill the rank function with a 4 for AL, 5 for FL and 6 for TX because it ranks them alphabetically.

To fill your A19:A23 you would just need a vlookup function that would return the state for the rank you are looking for.

Hope that helps.