Compare Two String Arrays Faster (Efficiency Challenge)

Hello all--

I need help speeding up the process of comparing two string arrays in order to find matches.

The below code works perfectly, however it is too slow. If the first array contains 5,000 strings and the second array contains 10,000 strings then the process could take well over an hour to complete (which is not good enough). My understanding of VBA programming is limited, however I do understand looping through each array by using nested loops is a common approach that works, but in this instance at least, is inefficient (maybe because it is comparing strings, or maybe an obvious rookie mistake i made?).

Would anyone be able to take this challenge of making this "Find Matches" section faster. Thanks for taking a look.

Note: This part of the code is the comparing/find matches section only, and is the part of my code that takes forever to complete (the declaration and array loading section of my code is not posted)

Please Login or Register  to view this content.

I agree with the above, a formula would be your best bet.

The issue with your code is that you aren't looping through arrays, you're looping through tens of thousands of worksheet calls, these are incredibly slow. You should be reading the lists into arrays and looping through those.

My original solution actually did use an excel formula but I ran into duplicate matches and missed matches so I abandoned the formula approach in favor of the VBA array comparison approach since I could tell it to remove any found matches from the replacement/donor array, and honestly, I had no idea it would take so long to process. Everything i read about VBA arrays led me to believe it would be extremely fast.

I don't mind at all to try another formula approach if you believe that is the faster approach.

Kyle--

Thanks for your feedback. I am looping through the arrays though (not the worksheet calls as you specify--i know that would be incredibly slow). I only reference the worksheet when a match is found, but not during my search. Unless I've made a mistake? I am for sure open to being corrected on this... I'm still learning.

ScrapeString and ScrapeStringDONOR are my string arrays

Please Login or Register  to view this content.

How many matches are there?

Kyle-- Potentially thousands. But even when few matches are found it is taking longer than expected. I will build a mock sheet to upload hopefully within the next few hours. (i don't have one made without sensitive info yet)

shg-- the strings are different length ranging from about 10 characters to sometimes over 30 characters. As for the dictionary approach, i'm open to trying, however, i am not sure the difference in an array versus a dictionary when attempting to compare them. Is there not a way to "test for Exists" with an array?

shg-- thanks for the ideas. In my google searching, i did come across a binary search, but failed to understand how it worked. And i stopped trying since several people mentioned that sorting an array of 10,000+ would take just as long as some other methods. Maybe i should look more into binary search afterall?

I think we need to see your data and what you are actually trying to do. Solutions are always context dependant and I'm afraid we're lacking any.

thanks Zero-- i will look more into the dictionary approach. i appreciate the link.

currently i am building a mock file that i can share with everyone in order to get a hands-on fix. should have it up in an hour or so hopefully.

Took me a bit longer to get freed up from work in order to build a mock file. I have it attached now. There are 1000 original IDs(column A) that need to be replaced with the Donor IDs(column C) based on their corresponding string information (the string information is what we are comparing). I have placed 900 matching strings in column D so there should be 100 non-matching ID's remaining.

For those readers starting from here: this method currently works, however, in my actual file it runs much slower, so i've been looking at ways to increase performance of the matching process.

Here's the thing: for some reason this mock file, even tho it is built exactly like my actual file, is extremely fast, usually completing the match in under 1 second. So there must be something up with my actual file, but i wanted to go ahead and post this mock file since i spent the time to build it, and perhaps this could help someone in the future with one of their projects.

I am also still very much open to better/smarter/faster ways of handling this matching process. thanks. -Bishop

See if this is fast enough,

Please Login or Register  to view this content.

Thank you all for the feedback. (i appreciate the tidbits Zer0, very helpful going forward).

I discovered my actual file had a rookie error in it that would access the sheet unnecessarily every single loop (it was built in to my if/then statement on a Else clause). Once i removed that, it fixed almost all my issues. Although i was looping through the two arrays, once a match was found, i then outputted to the sheet, which is another slow point (thanks to Kyle for bringing my attention back to this!). I have altered the actual file to output to another added column in my array (instead of the sheet) and then added another loop (after my matching loop finished) that took care of outputting the matches found to the sheet. These two changes had massive impact on the performance. I launched the updated macro to the team and everything is running above expectations.

I will still play with the performance based on the suggestions provided, but i will go ahead and mark this thread Solved. thank you everyone for your assistance. this is an amazing community and i very much appreciate the insight provided.

Zer0--

i must have been typing my most recent reply when you sent this message through. Thanks for your help. (also, i missed an acknowledgement for jindon's post--thank you. your code is admittedly above my limited coding skills, but i will learn more based on what you provided--so thank you very much).

So with my actual file and the mock file i provided, you are correct Zer0, the string columns that i am comparing will have duplicates. My unique ID's are the strings corresponding X or Y values (column A and C respectively). You are also correct about the matching being arbitrary to the extent that any of those 219 matches would work (and that's perfectly okay for my needs). I simply stop at the first match it finds, and return the Unique ID that corresponds with the string found. And, most importantly, in order to prevent duplicates on the next search, i erase the string in the Donor list.

Please Login or Register  to view this content.