Using IF and COUNTIF to show changes between 2 workbooks

Good morning!

I'm having a bit of a brain-freeze with excel this morning and wondering if anyone's able to point me in the right direction.

In short, the 2 problems I'm trying to solve with excel are:

1. Are any of the PROGRAM_KEY values in BEFORE present in AFTER but with their PROGRAM_ID values changed? ( It doesn’t matter if any PROGRAM_KEY values are missing between before and after)
2. Are there any PROGRAM_KEY values in AFTER that are not in BEFORE that have PROGRAM_ID values that are used in BEFORE

So far I've combined the 2 workbooks into two sheets, and tried a variety of IF and COUNTIF functions to try and get it to compare the data cross-sheet. The main problem seems to be the different number of values in BEFORE and AFTER (16k vs 28k), and what I'm trying to do (I believe) requires some kind of IF within the COUNTIF to give me the results I need. I was also having issues I believe because the PROGRAM_KEY's can contain colons and a mix of letters and numbers, so applying standard COUNT's to these seemed to be failing (excel also kept trying to convert it to number form and removing the prefixing 0's form the PROGRAM_KEYs).

Just wondering if anyone has any tips they could share on what I'm trying to do? - I'm willing to accept if excel isn't the right tool for no2. - Just seems to be getting me stumped at the moment!

I've uploaded the first 2000 rows of both spreadsheets (to keep the file size within forum upload limits), although they do have ~16k and ~28k in their entirety.

(all data has been desensitized before uploading!)

Many thanks in advance,
Rob

AFTER.xls
BEFORE.xls