+ Reply to Thread
Results 1 to 6 of 6

Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

  1. 06-11-2014, 12:25 PM #1
    AustinLe
    AustinLe is offline
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

    Hello Excelers!!

    I don't believe the title best explains my problem, but I will attempt to explain it here.

    In column A I have a list of the 50 US states, in B I have a list of numbers that corresponds to each state. These numbers will be updated daily some increasing a lot and some not at all. Anyways, I want to create a "top 5 list" that will update depending on which states have the highest counts. Linked to that list will be a simple pie chart that I also want to be "dynamic".

    The main issue I run into is the repetition of states that have the same numbers. This all becomes more clear in the sample document. Look in column G for what I've attempted so far.

    Any help and suggestions are very welcome!

    Multiple Sorted Match Results.xlsx
    Reply With QuoteRegister To Reply
  2. 06-11-2014, 01:07 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

    I think use of PivotTables would make your project look much more professional. It would be easy to maintain it and updating it would be just a click of a button.

    Please see attached file.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 06-11-2014, 01:57 PM #3
    AustinLe
    AustinLe is offline
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

    AlKey,

    Thank you for your response.

    Sadly, I'm not well acquainted with pivot tables, where can I input my new data that comes in daily? I'm basically keeping a running tally for each state and the pivot table wont allow me to change any of its values. I'm hoping to keep this data behind the scenes and pull the charts i'm making into a "report" worksheet for viewing.

    I added a sample of what kind of incoming data I'm getting (dumbed down a lot)

    Multiple Sorted Match Results.xlsx
    Last edited by AustinLe; 06-11-2014 at 02:05 PM.
    Reply With QuoteRegister To Reply
  4. 06-12-2014, 07:12 AM #4
    micope21
    micope21 is offline
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

    Hi

    E17
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    copy down
    D17
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    This is array formula Ctrl+Shift+Enter Not Enter. copy down

    Do the same top 5

    H17 Same above E17 Large copy down

    G17
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Ctrl +Shift+Enter then copy down.

    Regard
    micope21
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".
    Reply With QuoteRegister To Reply
  5. 06-13-2014, 09:37 AM #5
    AustinLe
    AustinLe is offline
    Registered User
    Join Date
    06-05-2014
    Location
    Iowa, USA
    MS-Off Ver
    2013
    Posts
    33

    Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

    Micope21,

    I appreciate you taking the time to put together those formulas for me, they all worked perfectly.

    May I ask what the easiest way would be to sum any and all remaining states and throw those in the chart as an "others" category? Of course keeping the dynamic nature of the formulas?
    Reply With QuoteRegister To Reply
  6. 06-14-2014, 03:49 AM #6
    micope21
    micope21 is offline
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Formula to Index, Match, Rank, and Sort a Dynamic Range of Values

    Hi

    Not sure what you mean? If you looking for total? use sum like this =SUM($b$2:$b$13)

    Regard
    micope21
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] dynamic index match formula to transpose values across rows and then down columns
    By Bananas212 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-30-2014, 03:07 PM
  2. Volatile formula with index/match dynamic range
    By asgersax in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-16-2013, 05:37 PM
  3. INDEX/MATCH and RANK formula array querys
    By pixifaery in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-16-2010, 09:12 AM
  4. dynamic range for index/match formula
    By excellicious in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2008, 07:41 PM
  5. Simplifying RANK,MATCH, INDEX Formula
    By ChemistB in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-06-2007, 10:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1