Flag up purchases made when the card was in a different location to associated vehicle

Hi everyone, looking for a formula to see if a purchase made against a vehicle reg (column transaction VRN in the purchase data tab) is between a two vehicle stops in the stop data.

In the example the purchases in green on the purchase data tab can be verified as the vehicle was stopped at that time at the petrol station. I have a formula already to see if the vehicle stopped at the petrol station, but need a formula to verify the purchase.

The objective is to flag up purchases made using the vehicle's card when the associated vehicle wasn’t there.

There is a suspicious purchase in this dataset - row 4 in the purchase data tab - this is suspicious as at this point the card was used but the vehicle was not stopped at the petrol station.


Thanks!!!!

UPDATE 14.04.2020 - now included formulas to highlight whether vehicle has entered petrol station geozone

I have a formula already to see if the vehicle stopped at the petrol station

Hello ProblemSolver and Welcome to Excel Forum.
It may help us to see the aforementioned formula, as I can not find any formulas in the file attached to post #1.
It would seem that if that formula doesn't indicate the vehicle stopped at a petrol station that would flag the purchase, but without seeing the formula we have a hard time understanding how that would work.
Let us know if you have any questions.

Perhaps this will do what you want:
1. In column R the formula to flag a suspicious purchase is: =SUMPRODUCT(('Stop Data'!B$2:B$127<=S2)*('Stop Data'!C$2:C$127>=S2)*('Stop Data'!G$2:G$127)*('Stop Data'!H$2:H$127))=0
Note that the formula returns true to flag the purchase
2. A helper column is added in column S using: =DATE(MID(D2,7,4),MID(D2,4,2),LEFT(D2,2))+TIMEVALUE(MID(D2,12,8))
The formulas in columns G:I on the Stop Data sheet have been modified
Let us know if you have any questions.

Include the vehicle number in column T using: =INDEX('Post Code Look Up'!M$2:M$3,MATCH(C2,'Post Code Look Up'!L$2:L$3,0))
Note that formula references a table on the Post Code Look Up sheet (columns L:M)
The formula in column R is modified to read: =SUMPRODUCT(('Stop Data'!B$2:B$335<=S2)*('Stop Data'!C$2:C$335>=S2)*('Stop Data'!G$2:G$335)*('Stop Data'!H$2:H$335)*('Stop Data'!A$2:A$335=T2))=0
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.