Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st One

**adamdeeakaaj** · 11-15-2016, 07:27 PM

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 on my attachments, 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!

**JeteMc** · 11-15-2016, 08:02 PM

Welcome to Excel Forum.
Please upload a sample of the spreadsheet rather than screen shots of the spreadsheet. For one thing the first and third screen shots are hard to read. For another it is easier to test possible solutions on a spreadsheet so that it is more likely to get a working solution sooner rather than later.
To attach a sample workbook, make sure there is enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (the paperclip icon doesn't work).
With all of that said, and if you want to work further on the issue yourself, it sounds like an Index/Iferror/Small or Index/Small(If... combination will perhaps give you a more suitable formula for your needs.

**adamdeeakaaj** · 11-17-2016, 11:18 AM

Here is the 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!

**JeteMc** · 11-18-2016, 10:45 PM

Waited for a while to see if 'another pair of eyes' could spot something that I can't.
The only thing that I can see, which is probably trivial, is that the formula in Export Tab!I8:I9 isn't the same as in I4:I7.
I am left to guess that you would like the Export Tab!I9:L9 to be filled with the values from Accounts!N9:Q9. Seems that the reason that doesn't happen is that Accounts!S8 = Accounts!S9 and the formula in Export Tab column A precludes that type of duplication.
There may be something that I am missing, if so please provide the information.
To me it seems the issue is about logic, as in another differentiating factor needs to be added to the values in column S of the Accounts sheet. Unfortunately I don't know enough about the workbook to offer a suggestion as to how that issue could be addressed.

**alansidman** · 11-19-2016, 03:43 PM

crossposted: http://www.excelguru.ca/forums/showt...out-Duplicates

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**Pepe Le Mokko** · 11-20-2016, 04:05 AM

Also cross posted at http://www.mrexcel.com/forum/excel-q...uplicates.html

**adamdeeakaaj** · 11-21-2016, 10:58 AM

Sorry y'all. Yes, this was cross-posted at the above links. Does anyone have a solution? Thank you!

**JeteMc** · 11-21-2016, 01:08 PM

In the file attached to post #5 the results that are currently being given are shown. What might help is to also show the results that you want displayed as well as a brief explanation of what criteria should be used.

**adamdeeakaaj** · 11-21-2016, 01:31 PM

I don't see any file attached to post #5. Is there a reason for this? Thank you Jete!

**JeteMc** · 11-21-2016, 01:44 PM

Sorry I was referring to the file Test 5.xlsm attached to post #3.

**adamdeeakaaj** · 11-21-2016, 02:08 PM

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!

**JeteMc** · 11-21-2016, 09:57 PM

Couldn't come up with a 'Clean' solution, that is one formula that could be entered in J4 and copied down and across to M8. This proposed solution has three parts actually, column J is populated by using the array entered formula:

Formula:

Please Login or Register  to view this content.

Columns K and L are populated using the array entered formula:

Formula:

Please Login or Register  to view this content.

Column M is populated using the array entered formula:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Another thing that I should mention is that the formulas don't block duplicates (K4 and M8) so a conditional formatting rule is applied to the range J4:M8 which matches the font to the fill, in this case yellow, and hides the duplicates using the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**adamdeeakaaj** · 11-30-2016, 12:44 PM

Jete, I just wanted to thank you very much for the help! Sorry, we've been very busy in the office. The formulas worked on our test spreadsheet, and we will be inserting into our main spreadsheet now! Again, thank you immensely for the help!!!

Best Regards,
AJ

**JeteMc** · 11-30-2016, 01:41 PM

You're Welcome and thank you for the feedback. Please take a moment, when you are able, and mark the thread as 'Solved' using the thread tools link that is above your first post. I hope that you have a blessed day.

**adamdeeakaaj** · 12-01-2016, 11:19 AM

JeteMc - I only noticed one small issue. The formula holds true except for one scenario. In row 9, where there are 4 registration holders in total and one registration was already pulled in column I , it counts one regiration holder twice and leaves one out. I tried to paste the formula into column it but that didn't work. I then tried to break down the formula to better understand it and see if I could modify it for column I but your wizardry is way beyond me. Thoughts on this column I issue?

**JeteMc** · 12-01-2016, 11:53 AM

I not sure what you are referring to. In my copy of the spreadsheet, attached to post #12, there are four registration holders listed in row 8 of the export tab, which match the four names in row 9 of the accounts sheet.

**adamdeeakaaj** · 12-01-2016, 12:09 PM

I'm sorry, I must have transcribed something wrong. It was doing a duplication of Iris on my end. Sorry for the hassle.

**JeteMc** · 12-01-2016, 12:18 PM

Not a problem. I hope that you have a blessed day.

Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st One

LinkBack

Thread Tools

Rate This Thread

Display

Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st One

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Re: Index Match Function To Pull Multiple Consistent Rows of Information Rather Than 1st O

Thread Information

Users Browsing this Thread

Similar Threads

trying use index match to pull information from database to individual sheet

Pull rev letters from index(match), multiple rows of same DWG# each diff rev

Index match function without array formula for multiple rows.

[SOLVED] Excel 2007 : How do I use Index/Match formula to pull certain information?

Pull Information off of database with INDEX, MATCH into a form.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions