# Can this approach be optimized?

Microsoft Office 2013 professional produ...
November 20, 2015 at 12:17:11
Specs: Windows 8.1
 Hello allCan the following solution be optimized somehow or am I stock with this very slow working array formula?I have a sheet with the following data:``` A B C 1 111 2011 1 2 111 2012 1 3 111 2013 2 4 222 2015 1 5 222 2011 1 6 333 2014 1 7 333 2011 2 8 333 2012 3 9 444 2015 1 10 444 2012 1```The first column is ID numbers, the second column is a year, the third column is a priority number, where 1 is first priority and so on. What I need is a solution that returns either the year 2011 or the year 2012, based on which priority number they have. 2011 is also first priority, so if 2011 and 2012 for a given ID number has the same priority, then 2011 has to be returned, but if 2012 has the highest priority, then that has to be returned. 2011 and 2012 will always be in the data set for all ID numbers, so no worries there.I then have those ID numbers (one of each ID number) in a separate list in the same sheet and in the column next to it, I have the following array formula to get the year I need for that particular ID number:```{=INDEX(B:B; IFERROR(MATCH(1;(I1=A:A)*(1=C:C)*(2011=B:B);0); IFERROR(MATCH(1;(I1=A:A)*(2=C:C)*(2011=B:B);0); IFERROR(MATCH(1;(I1=A:A)*(3=C:C)*(2011=B:B);0); IFERROR(MATCH(1;(I1=A:A)*(1=C:C)*(2012=B:B);0); IFERROR(MATCH(1;(I1=A:A)*(2=C:C)*(2012=B:B);0); MATCH(1;(I1=A:A)*(3=C:C)*(2012=B:B);0) ))))))}```It surely does what I want, but it is very very slow!Any suggestions on how to make this more efficient or maybe another approach?Thanks a lot in advance/Jacob

See More: Can this approach be optimized?

#1
November 20, 2015 at 12:35:14
 Without even trying to understand what your doing,change your formula so you DO NOT use the entire columns.Do you really need to search all 1,048,576 rows?Just use the range of data required. MIKEmessage edited by mmcconaghy

Report •

#2
November 20, 2015 at 13:05:00
 How slow is slow? I replicated your example data across 100,000 rows.I entered 444 in I1 and it took less than a second to return 2012.Is that not a valid test?

Report •

#3
November 20, 2015 at 13:11:59
 How much data do you have?What else is going on in the sheet?The formula is quick on my sheet with only the ten lines of data.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
November 20, 2015 at 15:00:45
 Not sure it will speed things up, but one way to shorten the formula would be to use a helper column and combine the Priority Number and Year.Something like:``` A B C D 1) 111 2011 1 12011 2) 111 2012 1 12012 3) 111 2013 2 22013 4) 222 2015 1 12015 5) 222 2011 1 12011 6) 333 2014 1 12014 7) 333 2011 2 22011 8) 333 2012 3 32012 9) 444 2015 1 12015 10) 444 2012 1 12012 1) 111 2011 1 12011 ```Column D was created using the formula =VALUE(C1&B1)Then the formula becomes:=INDEX(B1:B10,IFERROR(MATCH(1,(I1=A1:A10)*(12011=D1:D10),0),IFERROR(MATCH(1,(I1=A1:A10)*(22011=D1:D10),0),IFERROR(MATCH(1,(I1=A1:A10)*(32011=D1:D10),0),IFERROR(MATCH(1,(I1=A1:A10)*(12012=D1:D10),0),IFERROR(MATCH(1,(I1=A1:A10)*(22012=D1:D10),0),MATCH(1,(I1=A1:A10)*(32012=D1:D10),0)))))))Shorter, unknown if it's quicker.MIKEhttp://www.skeptic.com/

Report •

#5
November 20, 2015 at 16:38:16
 This formula seems to work, it uses a function, =DMIN(), I have not used very often.Your data will require headings, they are used by the formula.So with your data looking like:``` A B C 1) ID Nmbr Year Priority Nmbr 2) 111 2011 1 3) 111 2012 1 4) 111 2013 2 5) 222 2015 1 6) 222 2011 1 7) 333 2014 1 8) 333 2011 2 9) 333 2012 3 10) 444 2015 1 11) 444 2012 1 ```To do the query you will also need the column headings.So your query area will look like:``` I J 1) ID Nmbr Year 2) 444 ```In cell J2 enter the formula: =DMIN(A1:C11,J1,I1:I2)Now when you query an ID number, in cell I2, you should get the result your looking for in J2.I have only tested it on the 10 lines of data you supplied, so I'm not 100% sure it will work in all instances.Also, I do not know if it is any quicker then when you already are using.MIKEhttp://www.skeptic.com/

Report •

#6
November 21, 2015 at 05:13:38
 Hello again.Thanks for all your replies. I hornetly thought that the function didnt check all rows in the sheet just because A:A is used instead of A1:A1000. I thought that there was some sort of "smart"-function build in, that would stop the search or skip empty rows ect.I tried to put in a max of 100000 rows and that just took 2 seconds to go though like DerbyDad wrote.Thanks for that.The DMIN approach I will take a look at to see how that compares to the current formula. I will report back on that.

Report •

#7
November 21, 2015 at 06:26:30
 The DMIN() function simply examines a list of information and produces the smallest value from a specified column.The point I was trying to make, at least with the ten rows you posted, was that the Priority Number was not necessary to arrive at a correct answer.Simply finding the lowest Year value worked.Not sure how this is applicable to what your doing. MIKEhttp://www.skeptic.com/

Report •