Using Loops - Is there a better way to finding matching records?

Hi, I want to match records in one sheet to another to identify which records don't exist in the second sheet (as opposed to already existing there) so I can add them to that sheet. I've written the following code which is basically two loops looking for a match on the first field and then the second field. Is there a better approach to this than using loops? I have previously use the FIND functionality which I understand to be faster than loops but couldn't work out how to use it with 2 criteria. I've also appended a file with the code in.Looping to find matching values.xlsm Thanks for any ideas, regards, Neil

Please Login or Register  to view this content.

What are the matching column/s ?
A, A&B, A,B&C?

I'm trying to match the first column (Date) and the fourth column (Mem Name)....

Try the attached

Hi AB33, thanks for the input - the updating should be done from Data to "MemDataLog" - I reversed the references to the sheets and it works. I'll give it a thorough check...

I don't quite understand how it's working - I can see you are using arrays but I've no idea what " Set dic = CreateObject("scripting.dictionary") dic.comparemode = 1" is doing. Can you give me some explanation or point me somewhere I can read about the elements as I use the forum to fix things and for education! Presumably it's much faster than my loops solution?

Many thanks for the solution and your time, regards, Neil

MemDataLog has larger data, so the missing could be added to the small sheet which is sheet data.
The code uses dictionary object with arrays.
Dictionary are used to test for the existence of unique key. It is difficult to explain it in one page, but I would suggest, you should be comfortable with arrays before you jump in to dictionary.
dic.comparemode = 1 means the keys you are testing are not case sensitive, i.e. Key (A) and Key (a) are the same, but if change it the 1 in to 0, it becomes case sensitive.
Arrays are much faster than loops. You are basically writing(loading) the range value in to array and you then loop through each array to access the elements within the array. Finally you write it back(copy) in to range.

Read codes written by Jindon.

Thanks, a bit or reading ahead of me I can see! Once more thankyou for your time and help

Neil,
Do not be downhearted!
I learned from scratch(Zero knowledge about codding) in this site