Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Need Help with Excel Formula!


  • Please log in to reply

#1
KINNEY1978

KINNEY1978

    Member

  • Member
  • PipPip
  • 62 posts
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!
  • 0

Advertisements


#2
bomtond

bomtond

    New Member

  • Member
  • Pip
  • 4 posts
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.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP