Results 1 to 7 of 7

Thread: Index Match Function To Pull Multiple Rows Informations without Duplicates

  1. #1

    Index Match Function To Pull Multiple Rows Informations without Duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    So I have an issue I've been trying to resolve on a spreadsheet I created. I'm using the index/match/iferror function in order to populate information on a separate tab in my workbook. The formula I'm using is pulling the pertinent information, however, when it comes across a field name that is duplicated - it uses the first entry in the selection rather than all entries, which all the consistently named rows is needed to populate this portion of the spreadsheet. In addition, I would need this function to filter duplicate entries. So attached I have two screen caps. on the export tab and one from the accounts tab. The accounts tab shows columns AG-AJ, which are the client fields used to populate the information in the export tab under client name. Column AL on the accounts tab shows where information from column A in the export tab gets populated to consolidate fee schedules. If the fee schedule is the same as another household, it will get populated underneath a singular row rather than multiple on the exports tab. This is the basis for the index/match/iferror function, which looks up the fields in column A of the export tab and based on matching values on the accounts tab, pulls the pertinent client information. So the problem is it will not pull the multiple clients information if under different rows. It seems the function goes to the first instance of the value found and solely uses that information rather than all matching entries. After that is done, I need the function to not populate duplicate entries. Any help would be appreciated! Thank you very much!





    Updated: Attached is a sample workbook. The columns changed a bit, so the columns we are working on in the export tab starts with column I, and continues with the columns highlighted in yellow. Again as a reminder we are trying to populate the client information from the accounts tab that follow under the same pricing tier without duplicating entries. Thank you very much!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Excel CIS Screen Cap 2.PNG 
Views:	18 
Size:	41.5 KB 
ID:	6018   Click image for larger version. 

Name:	EXCEL CIS Screen Cap 3.PNG 
Views:	16 
Size:	66.4 KB 
ID:	6019   Click image for larger version. 

Name:	Excel CIS Screen Cap.PNG 
Views:	12 
Size:	46.3 KB 
ID:	6020  
    Attached Files Attached Files
    Last edited by p45cal; 2016-11-19 at 07:42 PM. Reason: made more readable by removing ineffective colour tags

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,693
    Articles
    0
    Excel Version
    365
    cross posted without links:
    http://www.excelforum.com/showthread.php?t=1163486
    http://www.excelforum.com/excel-formulas-and-functions/1163869-index-match-function-to-pull-multiple-rows-informations-without-duplicates.html?amp;goto=newpost


    adamdeeakaaj, for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

  3. #3
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,698
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Lso cross posted at http://www.mrexcel.com/forum/excel-questions/976676-index-match-function-pull-multiple-rows-informations-without-duplicates.html
    Thank you Ken for this secure forum.

  4. #4
    Super sorry guys. Yes, this was cross-posted to the above links. Does anyone have a solution? Thank you for the warning!

  5. #5
    OK. Sorry, thought this was clear as to what I was originally trying to achieve. Looking back, maybe not so much. Row 9 is not necessary in this example, as there is not another pricing tier (although if there was one, it would be utilized). I am going to attach an updated Excel Workbook with consistent formulas for columns I-L (can't include M-N as I'm not too sure how to utilize formulas for these columns yet.) Attached is pulling I4:L4, I5:L5, I6:L6, and I7:L7 correctly. There are a few problems. On I8:L8 it is only pulling one value (the first instance, N8:Q8 on the accounts tab, of the pricing tier only has one client). The second instance (found on the accounts tab N9:Q9) is not pulling into J8:L8, which is under the same pricing tier. We want that information to pull, as well as not duplicate names from the first instance. For columns M and N, these will be utilized if there are more than 4 entries underneath the same pricing tier (however it will never exceed 6, which is why we only have 6 columns for clients in the export tabs.) So for J8:L8 it should pull the names Accounts!N9:Q9 without duplicating the client "West-Allen, Iris." Furthermore, if there was an entry to Accounts!O8 that didn't duplicate any of the names in Accounts!N9:Q9, then we would need that extra name filled in Export Tab!M8. Hope this provides some clarification! Please refer to the new attached test workbook. Thank you very much!
    Attached Files Attached Files

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    I'm afraid I find it very off-putting when there is cross posting, as inevitably you find that you spend time designing a solution along with others, and all but for one, it will be a waste of time.

  7. #7
    Well the reason it is cross-posted is because no one has provided a solution yet. Also, I believe you could have kept that comment to yourself, however, I am very understanding of your position. I hope you also see my position as to why I (and possibly others) cross-post on forums. You're more than welcome to view the cross-post via the links and see that no response or solution has been provided yet.

Tags for this Thread

Posting Permissions

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