+ Reply to Thread
Results 1 to 10 of 10

Dynamic Sort via Index/Match/Aggregate

  1. #1
    Registered User
    Join Date
    10-03-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    17

    Dynamic Sort via Index/Match/Aggregate

    Trying to figure out if there is a low-impact way to add another variable into the Dynamic Sort that I've created in the attached example workbook. I've figured out how to dynamically sort by date/project name/amount via the index/match/aggregate function (with help from this forum) in the cells H6:K13, but want to see if there is an easy way to add 'Team' as a variable into the sort utilizing a driving cell (H7 in this case) along with driving date cell (H6).

    Would love to keep the current functionality along with the ability to sort by Team.

    Thanks in advance for your help.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Dynamic Sort via Index/Match/Aggregate

    If you're looking to filter the data where Team = H7 then add the below test immediately prior to the ,ROWS( reference

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    repeat the same on each column in which you have a formula

  3. #3
    Registered User
    Join Date
    10-03-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    17

    Re: Dynamic Sort via Index/Match/Aggregate

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    repeat the same on each column in which you have a formula[/QUOTE]

    Thanks for the suggestion. I tried including this test, but this doesn't allow the functionality to pull in all of the projects from the specified time period to be pulled in (i.e. It only allows one project from Jan-19 to be pulled in versus grabbing all of the largest Jan-19 projects)
    Last edited by alansidman; 11-14-2018 at 12:00 PM.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Dynamic Sort via Index/Match/Aggregate

    Per the initial post I interpreted the requirement as being:

    - return those Projects where the Date (Col B) occurred in Month specified in H6, and where Team (Col D) matched that specified in H7
    - given sample data there would only be one Project that matched that criteria for Team VP1
    - were H7 to change from VP1 to VPN2 three Projects would be returned

    Could you please confirm the specific criteria you wish to apply (and relevance of H6/H7) when creating the list, as my interpretation of your requirement is clearly incorrect

    p.s. please don't quote entire posts -- you'll get into trouble with the Mods!

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Dynamic Sort via Index/Match/Aggregate

    @datbigdog

    Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    10-03-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    17

    Re: Dynamic Sort via Index/Match/Aggregate

    Thanks for the PS - did not know this was such a big faux pas.

    Also realized it didn't work because I'm an idiot and didn't realized there was only one combination that fit the selection criteria until just now. Thanks and repped!

  7. #7
    Registered User
    Join Date
    10-03-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    17

    Re: Dynamic Sort via Index/Match/Aggregate

    Apologies for digging up an old thread, but I had an add-on question to the one that was answered here: is there a way to take this dynamically sorted list (in the bordered in section on the 'Summary' tab) and re-organize in descending order by the 'Amount' column?

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Dynamic Sort via Index/Match/Aggregate

    attached is a pivot table

  9. #9
    Registered User
    Join Date
    10-03-2018
    Location
    Minneapolis
    MS-Off Ver
    2016
    Posts
    17

    Re: Dynamic Sort via Index/Match/Aggregate

    Is there a way to add an attribute into the existing INDEX MATCH function within the bordered area that sorts it by amount rather than just pivoting out the information?

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Dynamic Sort via Index/Match/Aggregate

    A 'low tech' proposal would be to use the existing bordered section as a helper, perhaps hidden or moved out of sight, and then apply the following:
    1: The 'Amount' column is populated using: =AGGREGATE(14,6,AG$10:AG$14,ROWS(A$1:A1))
    2: The other columns are populated using: =INDEX(AD$10:AD$14,MATCH($K10,$AG$10:$AG$14,0))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index/Aggregate/row match with conditions, pivot summary replacement
    By Shruder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2018, 08:36 AM
  2. [SOLVED] Dynamic INDEX/MATCH
    By bigjdawg43 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-25-2018, 06:18 PM
  3. Index/Match - Dynamic Index Range with Hlookup
    By WassimJMP in forum Excel General
    Replies: 3
    Last Post: 02-16-2016, 12:24 PM
  4. [SOLVED] INDEX MATCH, MATCH and ADD for a dynamic scatter gram chart
    By julesmctavish in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-12-2016, 10:40 AM
  5. Index, Aggregate, Row - Removing Duplicate W/ Index Help
    By adbexcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-04-2016, 03:53 PM
  6. Formula to Index, Match, Rank, and Sort a Dynamic Range of Values
    By AustinLe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2014, 03:49 AM
  7. Replies: 6
    Last Post: 11-08-2013, 10:29 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1