Retrieve match from array items optimize time

Hello everyone
I have this UDF (Narayan's from chandoo)

Please Login or Register  to view this content.

I intend to use that udf in a list of 200,000 rows ... How can I optimize that to make it faster?

Thanks a lot for advanced help

Weirdly-built function. What are you trying to return? Currently it returns only the furthest-in first match of any of the items, so if there's multiple instances of the same code it may not return what you expect. Is this correct?
It also returns an 'illegal' Excel error code 0, meaning the error return triggers another error which then defaults to an error of #VALUE (actually code 2015, or xlErrValue). The error #N/A (code 2042 or xlErrNA) is more applicable to the string not being found, like when VLOOKUP cannot find a match.

Thanks a lot for reply
There's no problem. If not match found I could put empty string instead of CVErr(0)
The main problem is how to apply such idea on large amounts of data..

As for your question .. yes I would like to return only the furthest-in first match of any of the items

Running the UDF on 200,000 cells only took 2.2 secs for me. I was able to shave it down to 2.0 secs by declaring your array explicitly.

Please Login or Register  to view this content.

Code for testing with calculation method set to manual and autofilling formula each time (so that it must be calculated):

Please Login or Register  to view this content.

If you do it purely in VBA you can get it down to 1.2 secs. Your decision if it's worth it *shrugs*

Please Login or Register  to view this content.

Hi,

Why use a UDF at all? I suspect a formula would be faster. Perhaps
=IFERROR(MID(A1,AGGREGATE(14,6,FIND({"UY","OP","ST"},A1),1),2),"")

That is really awesome. Thank you very very much for this great solution
Best Regards

Originally Posted by xlnitwit

Hi,

Why use a UDF at all? I suspect a formula would be faster. Perhaps
=IFERROR(MID(A1,AGGREGATE(14,6,FIND({"UY","OP","ST"},A1),1),2),"")

That works great too but it may grab the substring of the whole string
Example : "HelloUY Good" >> It return "UY" .. It would be preceded with space and when trying " UY " it returns "U" only

It is OK I have changed 2 at the end of the formula to 3 and it worked. Thanks a lot for great help

A modification to nitwit's clever formula for your purposes:

Formula:

Please Login or Register  to view this content.

Can confirm it returns the same result as UDF and VBA for these test strings:-

TARGET	UDF	FORMULA	VBA
this string contains nothing	#N/A	#N/A	#N/A
this string contains ST and UY	UY	UY	UY
this string contains ST and UY and ST	UY	UY	UY
this string contains ST and UY and ST and OP	OP	OP	OP
this string contains ST and UY and ST and OP and ST	OP	OP	OP

---

If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
Thanks!