I think I'd try to tackle this in the following manner:
- Split "Your" data to one sheet and "Client" data in another
- Define a named range to cover "Your" data
- Put "Client" data starting in column B
- Fill "Client" data column A with a formula to identify if the item is found or not
- Use an Autofilter to delete all "not found" rows
So the trick here would be to figure out what the formula needs to be in order to have it correctly return Found or Not Found.
- Is the client number always 6 digits long?
- Does it always start with a letter followed by 5 numbers?
- Is there always a Total row in the client data?
- Is there always a blank row after every total row?
- Is there always a blank row in row 2?
Now, I haven't split the sheets up, but if the questions above are all true, you could throu the following formula in D2 and copy down:
That would give you repeating blocks of Found/Not Found. From here you could use Autofilter to filter the "Not Found" records then delete them. (And yes, we could use code to make that automatic.)
=IF(OR(OFFSET(E2,-1,0)="Posting Date",OFFSET(E2,-1,0)=""),IF(ISNA(MATCH(E2,$B$3:$B$11,0)),"Not Found", "Found"),DOFFSET(D2,-1,0)
Just in case you're wondering about the formula, I used OFFSET to make the formula a bit more dynamic. This way you can delete the row about and it won't blow up the formula.
At any rate, let me know if you think that would work and we can look at automating the process.