Transform User Defined Function to Sub (Filter Partial Concatenated Match) SOS

Hello,

I'm running in circles. I've built this dashboard over a bunch of concatenated SKU placed in column. Each string vary the number of substrings, but every code has 34 lenght. A great human being also from this community helped me with this user defined function below. The code does exactly what I need: it loops through column "P" (SKUs) and search for partial matches within each code for every partial code that is referenced in another column.

For instance, in this other column I have the partial codes: "005P" 039P" 0154P"

The code will search for these 3 values within every substring of each cell of column "P", and then will return into another column ("M") the same array but showing only the match cases instead.


So if I had:

Please Login or Register  to view this content.

It'd only return

Please Login or Register  to view this content.

The script would be perfect if it didn't consume too much processing. The file became useless because it takes too long to update. I've tried everything I could think of, I simplified the formulas, switched every vlookup for index, tried to be the less redundant as possible, but it's still too slow.

The way it is right now, I have these listboxes that will trigger a true/false statement that populates the reference column for the partial codes. The listboxes are set to trigger manual calculation as a band-aid, and when the user is done selecting the filters he presses a "update button" that turn calculation back to automatic. This band-aid is due to the fact that I assumed a conflict was happening when clicking the listbox and the user function would update at the same time the column is being populated.
My guess is that using a sub code could be more efficient and maybe save me sometime. Right now it takes about 15-20 min to update every time. If I could take it down to 5 min I'd be glad already.

Anyone has an opinion on this?

Please Login or Register  to view this content.

Hey Jindon, I tried the code and it stuck more than 10 min so I restarted excel. I made some changes tho, not sure if that could be the problem.

As the button is located in a different sheet from the output column for the results, I added

Please Login or Register  to view this content.

I thought it'd be the way, sorry if it looks stupid


And 1 question, where does the code makes reference to column "m" as output result?

it didn't work, or at least for the 10 min waited. I think I'll try some things with the user function as I managed to get some improvement after I did this post

Thank you very much for your interest and help, sorry if I made you waste your time, but I will keep this code to further testing in the future

I will upload. Just let me rush on some things I need to fix and remove sensitive data and I will come back soon with a file.

Yes, for some for reason after I made some changes it works well now.

Thank you very much jindon, your code still useful and I kept it for any future needs