Can this approach be optimized?

Microsoft Office 2013 professional produ...
November 20, 2015 at 12:17:11
Specs: Windows 8.1
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	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?

Report •


#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.


MIKE

http://www.skeptic.com/

message 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?

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


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.

MIKE

http://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.

MIKE

http://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.

MIKE

http://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.

MIKE

http://www.skeptic.com/


Report •

Ask Question