Problem with array pulling in duplicate data
So currently I am using the array below to pull in duplicates of data. It only works when I have the code in column E, the data # I am looking for in column D starting at row 4 and the pool of data I am pulling from in columns A and B starting at row 4. I have tried moving the array around and changing the numbers in the array for rows and columns. When I do that it either won't work or gives me incorrect data.
Please Login or Register to view this content.
So, where do you want it to start?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Do you really need 600,000 elements in those ranges? An array formula will look at each cell in that range, so it is better to make the ranges as small as possible.
Hope this helps.
Pete
I just want to be able to move the array around so I am not always having to put the data in those exact places. And yes, I am sure I could reduce the number of rows.
Since you are using the 2016 version you could use AGGREGATE(15,6... in place of SMALL(IF... and not have to use Ctrl, Shift and Enter to confirm. I.E.Formula:
Please Login or Register to view this content.
Using COLUMN(A:A) as the [k] argument to the AGGREGATE function will start you with the smallest row in which there is a match, then as you drag to the right the next cell's formula will read COLUMN(B:B) which yields the second smallest row number in which there is a match and so on.
I might help us to help you if you could upload a small desensitized example of what you are attempting to accomplish. To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks