macOS Automator to delete specific Excel rows

Would probably be easier to write Visual Basic code inside Excel for this. I am learning some VB automate some Excel functions.

if you can save as csv (or tsv, etc), this could be done with awk in terminal

Well, agree with Sero that awk would work. Join would also work if you have the UNIX utilities.

In Excel you can use MATCH and/or VLOOKUP. Syntax is MATCH(longer list ID, shorter ID list). If the longer list ID is in the shorter ID list, it will return the row number in the shorter ID list of the longer list ID, otherwise it will return #N/A.

VLOOKUP works in similar fashion, but allows you to grab another field from the shorter list (i.e. 2nd column) if there's a match on the id. If the shorter list is unordered, use FALSE as the last argument in the vlookup call.

All the rows with #N/A returned are rows in the longer list with no match in the shorter list. Sort the longer list by whether the function returned a match or #N/A, which would put all the unmatched rows in the longer list together to be deleted.

Have fun,

!