+ Reply to Thread
Results 1 to 15 of 15

Create List - Index Match or Index Aggregate or other?

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Create List - Index Match or Index Aggregate or other?

    Hi once again,

    I am so frustrated with this - this is beyond my scope atm. I just do not know how to create a list based on meeting some criteria.

    I know it will be quite easy for those well versed with excel.

    TIA
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,370

    Re: Create List - Index Match or Index Aggregate or other?

    No idea at all what you're trying to do
    I'm on Windows but that's no issue but I tried to figure out what you are trying to do and I'm sorry, I don't see it.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    use drop down
    populate Parts, Line Codes, Net Sales list using the Account ID

    Junk at bottom is stuff I am experimenting with
    Attached Files Attached Files
    Last edited by bambamclint; 02-08-2019 at 06:28 PM.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,370

    Re: Create List - Index Match or Index Aggregate or other?

    Okay got the idea.
    If you need a list then I don't think a formula will work, you would have to resort to macro's or just add a pivot table with the required fields with and filter that
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    I was hoping to avoid a pivot table even though it does work and gets the "desired" result. Appreciate the response.
    Can the values (Line_Code & Net Sales) be returned in the same column so that table is the same size?


    Could one not "count" the number of rows based on the Account ID or Shop Name and return those through a formula?

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,370

    Re: Create List - Index Match or Index Aggregate or other?

    Yes but the problem is the increment and I wouldn't know how to do it with formulas

  7. #7
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    Me either - thats why I am here LOL

    I saw someone using Index/Aggregate but I was completely lost.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,515

    Re: Create List - Index Match or Index Aggregate or other?

    Please try at
    Summary!A8 drag across and down

    =IFERROR(INDEX(primary_lines,AGGREGATE(15,6,ROW(primary_lines)/(Hard_Parts!$B$2:$B$200=$A$2),ROWS(A$8:A8))-1,MATCH(A$7,primary_lines[#Headers],)),"")

    I edit the header to match with Hard_Parts.
    Attached Files Attached Files

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,370
    Quote Originally Posted by bambamclint View Post
    Me either - thats why I am here LOL

    I saw someone using Index/Aggregate but I was completely lost.
    LOL never hurts, Iíll look and see if I can find something
    Macros will surely make it work though

  10. #10
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    Bo_Ry,

    That seems to work like a charm! I have spent a lot of time on this and would have never have come up with that. Really Appreciate it

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,515

    Re: Create List - Index Match or Index Aggregate or other?

    Happy to help

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  12. #12
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    I will once I am able to test it in the environment/workbook that I built. I need to get my Swim Club registration packages done.

  13. #13
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    It sort of works - I am not sure how and what values it searches for but it is not pulling all the values over

    I have attached the sample workbook with real data.

    For example, if you select Rolling Mix Concrete the results start at the 21st entry (world Parts Axles/$197.xx).
    Another example: Hicklin Motors has data but no results are shown.

    What would cause that to happen?
    Attached Files Attached Files

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,515

    Re: Create List - Index Match or Index Aggregate or other?

    Correction as in Red at A8 drag across and down.

    =IFERROR(INDEX(primary_lines,AGGREGATE(15,6,ROW(primary_lines)/(Hard_Parts!$A$2:$A$4000=$A$4),ROWS(A$8:A8))-1,MATCH(A$7,primary_lines[#Headers],)),"")
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-24-2013
    Location
    canada
    MS-Off Ver
    Windows
    Posts
    20

    Re: Create List - Index Match or Index Aggregate or other?

    You have made my day! Thank you1

+ 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. Dynamic Sort via Index/Match/Aggregate
    By datbigdog in forum Excel General
    Replies: 9
    Last Post: 06-15-2019, 09:02 PM
  2. [SOLVED] Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function
    By remyte in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2019, 01:53 PM
  3. Replies: 2
    Last Post: 12-05-2018, 07:59 AM
  4. [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
  5. INDEX/MATCH to create a list with multiple criteria
    By Alphabex in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2018, 08:03 AM
  6. 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
  7. [SOLVED] Help to create a Index / Match list of unique values.
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-16-2013, 04:44 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