Neptune IT
asked on
Index Match returning correct result but slow. Need assistance optimizing.
Greetings,
I am trying to create a single page lookup for server data on an excel sheet. When the server name is selected via a drop down and the name selected is placed in A2, what should follow is it's relevant information.
First three columns are simple vlookups and I doubt that they would be slowing it down too much.
I crafted an array to lookup the multiple passwords. It works, but it is very slow and my boss wants it to pop quickly. Here is the array:
I am not as proficient in VBA as I would like to be so what I am looking for is a VBA equivalent to this. Just to speed up the response time.
Any assistance is appreciated.
I am trying to create a single page lookup for server data on an excel sheet. When the server name is selected via a drop down and the name selected is placed in A2, what should follow is it's relevant information.
First three columns are simple vlookups and I doubt that they would be slowing it down too much.
I crafted an array to lookup the multiple passwords. It works, but it is very slow and my boss wants it to pop quickly. Here is the array:
=IF(ISERROR(INDEX('Server Passwords'!A:A:'Server Passwords'!B:B,SMALL(IF('Server Passwords'!A:A=$A$2,ROW('Server Passwords'!A:A)),ROW(1:1)),2)),"",INDEX('Server Passwords'!A:B,SMALL(IF('Server Passwords'!A:A=$A$2,ROW('Server Passwords'!A:A)),ROW(1:1)),2))
I am not as proficient in VBA as I would like to be so what I am looking for is a VBA equivalent to this. Just to speed up the response time.
Any assistance is appreciated.
Array formulas are remarkably resource hungry, especially as you have it looking at whole columns eg 'Server Passwords'!A:A
Do you really need to use the whole column, over 1 million rows of cells to be checked?
Can you change it to a fixed range?
If the data is frequently changing in size, you may be able to use Dynamic Range names that adjust with an update of data. Once the data update is complete the Range Name should only need to recalculate once.
Thanks
Rob H
Thanks
Rob H
Do you really need to use the whole column, over 1 million rows of cells to be checked?
Can you change it to a fixed range?
If the data is frequently changing in size, you may be able to use Dynamic Range names that adjust with an update of data. Once the data update is complete the Range Name should only need to recalculate once.
Thanks
Rob H
Thanks
Rob H
You must use smaller ranges. I suggest you to use Calculated Tables. You don't need to use whole "sheet column", it is enough to tell your formulas to look in "table column".
You can look here for information about creating and deleting tables
And when you use if(iserror(x),"",x) combination, it will first calculate the x, and if answer is false, it calculates x again to show you. You better use iferror function for this. It is enough to say Iferror(x,""). It will only calculate your formula once. If it is error, it will hide x as "".
You can look here for information about creating and deleting tables
And when you use if(iserror(x),"",x) combination, it will first calculate the x, and if answer is false, it calculates x again to show you. You better use iferror function for this. It is enough to say Iferror(x,""). It will only calculate your formula once. If it is error, it will hide x as "".
ok here it is change your formula by this one and for sure save it as array CTRL SHIFT ENTER and try it.
You may change the 100 to suit your range of users/passwords the less the faster.
gowflow
=IFERROR(INDEX('Server Passwords'!A1:B100,SMALL(IF('Server Passwords'!A1:A100=$A$2,ROW('Server Passwords'!A1:A100)),ROW(1:1)),2),"")
You may change the 100 to suit your range of users/passwords the less the faster.
gowflow
The IFERROR function came in with excel 2007 so is fine to use unless you need backwards compatibility for users still on 2003.
Thanks
Rob
Thanks
Rob
ASKER
Thanks for the help so far.
The problem is I have multiple arrays on my page which are slowing down the results immensely.
Unfortunately I can't post a workbook as it is sensitive material.
Will try to shorten what it is looking at.
The problem is I have multiple arrays on my page which are slowing down the results immensely.
Unfortunately I can't post a workbook as it is sensitive material.
Will try to shorten what it is looking at.
ASKER
Here is something similar though. This is the server password layout:
Server Password Type User Password
xxxx Local Admin administrator xxxxxx
There are 109 rows total.
What I am trying to get is each instance of Password type, User and Password that match the server name.
Server Password Type User Password
xxxx Local Admin administrator xxxxxx
There are 109 rows total.
What I am trying to get is each instance of Password type, User and Password that match the server name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As soon as I read yours and compared it to mine I wondered how I got this far being so naive :)
Thank you.
Thank you.
pls post a worksbook and explain in plain English what you want then will seek to it.
gowflow