Match two values between two list using two criterias

[sa02000]

Hi - I need help with a very complicated comparision / matching of two lists. I have tried to explain below and attached an example of how the match would look like. On top of being big lists I need to do it every month so trying to set up a repeatable process. Any help will be very highly appreciated.

I have two lists (both lists 100,000+ rows and I am using Excel 2013) and would like to identify entries based on two columns (column A and B on attached file) and then match values in two other columns (column C and D on attached file). Two lists are in separate files but can be in same file in separate sheet. So a macro flow will look something like this:

In list 1:
1. Identify all values in column A that have corresponding column B cell empty and value only appear once in Column A (lets call it group 1)
2. Identify all values in Column A that have corresponding column B cell empty and value appears more than once in column A (lets call is group 2)
3. Identify all values in Column A with value in corresponding column B cell (not empty) (lets call it group 3)

Now compare values in column C and D for group 1 in list 1 with values in column C and D of list 2 where column A values in list 2 equals column A value in list 1; return true or false.

Compare values in column C and D for group 3 in list 1 with values in column C and D of list2 where Column A and B of list 1 match column A and B of list 2; return true or false

Compare values in column C and D for group 2 in list 1 with values in column C and D of list2 where column A in list 2 equals column A in list 1 but excludes matching the values matched above in group 3.

Thank you in advance,
Jay