Comparing 2 separate columns in 2 separate Excel sheets

Hi All,

I'm not sure if this is the right part of the forum, I don't know if it's a formula or a macro I need. I have 2 separate Excel sheets where I need to compare 2 columns in one sheet to 2 columns in another sheet to find duplicates between the 2 sheets.
Here is the more detailed situation:
I have a listing that details subject visits. I need to contact doctors based on some of these subject visits.
Each month a new listing is pulled, and the Excel listing that shows these subject visits is only supposed to pull new listings, but we got a new programmer and they don't know how to do that yet, so they pulled the entire, several thousand visit listing. I have already contacted doctors based on most of these visits, which I have tracked in a separate log. I need to compare the tracking log to the full log to see which subjects were already contacted about which visit.

Let's say subject 1234 comes in for Visit 1, Visit 2 and Visit 3. I may have already contacted the doctor about Visit 1 and 2, but not Visit 3. I need a way to compare the 2 logs that tracks both subject number and visit name (ensuring, for example, "subject 1234, visit 1" is always linked together in each sheet). Going through it manually will take me a week, so I was hoping there was some kind of formula (or maybe macro?) that could help me do this, especially since my boss gave me a contradicting order on how to go about this. (contact the doctors for new visits, but don't review the log for it).

Is this something that's possible?

I have attached an example file, which is very similar to the real file in all but size. The real fie contains thousands of entries, so manual review would take a very long time, especially since a new listing is to be pulled soon. On the second tab (titled "Full Query File") I have highlighted in Green the issues that were already addressed per the 'Query Issued" tab. I would, ideally, like the full query file (tab 2) to highlight which issues were already addressed per the "Query issued" tab.

Thanks for your replies. I will need to get approval from IT to download PowerQuery, as I do not seem to have it installed. I tried the formula in the meantime and keep getting the #VALUE! error, though I copy+pasted exactly. Maybe I'm not linking it right? I am also unsure of what "CF" is.

Originally Posted by Olly

You can use PowerQuery to merge the two tables. Format data in both sheets as tables, named "QueryIssued" and "FullQuery" respectively.

Then use this query to load to the FullQuery table:

Please Login or Register  to view this content.

This will retrieve the Date Sent from matching Query Issued records. Note this looks for matches in all four fields: "Location", "Subject #", "Visit", "Issue #"

Finally, apply conditional formatting to highlight rows with Date Sent values.

Example workbook attached.

I needed some additonal help to understand how to work the code, but I mostly got it to work, thank you for your help!