With the data in the attached google sheet, Col F (IN/OUT) will have either IN or OUT and Col J (Bag Numbers) will have numbers seperated by Comma.

Based on the sample data, for Vendor 4-Sunil, Bag numbers 2,3,4,5 where sent OUT on 8/9/21. On 17/9/21, 2,3,4,5 where received IN. So there is nothing Pending with Vendor 4-Sunil.

On the other hand with Vendor 5-Rajan, Bag numbers 13,14,15,16 where sent OUT on 14/9/21. On 25/9/21 Bag Numbers 13,15 where received IN. So the pending bags yet to be received IN from Vendor 5-Rajan is 14,16.

Is there a way to build a formula in Google sheet to indicate the number of pending Bags based on IN or OUT criteria for a given Vendor?

Thank you.

Sample Google sheet