Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Leith Ross; 09-04-2014 at 04:05 PM. Reason: Added Code Tags
Do you have a specific question?
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Thanks for quick response - Is there a way we can make it fast ? I am trying to lookup for Tax Id , range (Tax Entity Code : OPIE) and return the OPIE values to col Formula. Please note that TAX_ID has multiple matches. can we avoid using UDF ?Many thanks
TAX_ID Formula Tax Entity OPIE
100 #NAME? 100 85005
101 #NAME? 101 81317
102 #NAME? 103 79601
103 #NAME? 103 82151
104 #NAME? 104 79701
105 #NAME? 104 82152
106 #NAME? 111 95104
107 #NAME? 112 82201
108 #NAME? 112 82202
109 #NAME? 112 86101
110 #NAME? 113 79602
111 #NAME? 113 82203
112 #NAME? 114 79603
113 #NAME? 114 95101
114 #NAME? 114 95198
115 #NAME? 114 79693
116 #NAME? 117 79602
117 #NAME? 117 82201
118 #NAME? 120 79611
119 #NAME? 120 82312
120 #NAME? 121 79606
121 #NAME? 121 82311
122 #NAME? 121 82313
123 #NAME? 129 82302
Last edited by vineet2k; 09-04-2014 at 01:05 PM. Reason: additional info
I think that, in order to speed it up, the best thing to do would be to rethink the algorithm. Currently, it appears that the algorithm looks at every entry in lookuprange and collects those that match. Naturally, a "linear" search like this is one of the slowest searches, though sometimes it is necessary.
Another thing I note is that it looks like it will search every single cell across all columns, rather than search within a single column. This further multiplies the number of "tests" the routine is performing.
The main way I see to speed this up is to think it through so you can reduce the number of operations. My first suggestion would be to require the user to sort the lookuprange based on the search column. This will do two things. It will allow you to use a much faster binary search algorithm and it will put all of your results together in a block within the lookuprange. With the data sorted, you only need to find the first and last entry containing lookupval, then your result will come from the block of cells in between. This approach will require your user to provide more input to the routine, but it will reduce the number of operations the UDF needs to perform -- significantly speeding up the execution.
Originally Posted by shg
As far as avoiding the UDF altogether, have you explored using filters for this? http://office.microsoft.com/en-us/ex...616.aspx?CTT=1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks