I will buy someone a coffee for helping me with this 🙂

I go through large (30,000+ line) Excel sheets every other week and I’m looking to automate some of the processes. For these sheets, I am looking to see if any codes are dropped or if they are duplicated (see attached image). I currently use a COUNTIFS function to check for any singular dropped codes, but for the more complex issues I am still finding that I need to look manually.

Is there a way (macro??) to do this? Please let me know if there are any aspects of this that I can clarify.

Examples of the types of issues that I would like to automate

I have tried with the COUNTIFS function to expand the rows that it looks at in hopes that it will take into account the code system name, but haven’t had good luck with that.
I’m not even sure where to start with trying to create a duplicate codes sheet, mainly because I want it to only look at rows that have white lines and compare it to the associated red/green lines.