Hello all Can 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 1The 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

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,576rows?Just use the range of data required.

MIKE

message edited by mmcconaghy

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?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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.

MIKE

Not sure it will speed things up, but one way to shorten the formula would be to use a helpercolumn 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 12011Column 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.

MIKE

This formula seems to work, it uses a function, =DMIN(), I have not used very often. Your data will

requireheadings, 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) 444In 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.

MIKE

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.

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.

MIKE

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History