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

1. ## 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

2. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

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.

3. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

Thanks JeteMc - I have now done this - let me know your ideas or thoughts.

4. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

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.

5. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

Hi JeteMc,

Many thanks for the above.

However, I have a further issue which I am struggling with - how do i verify the purchase was made when there are multiple vehicles and stop times and purchase associated with each vehicle? I have added some more dummy data for another vehicle and another suspicious purchase.

The Card VRN in the purchase tab = Vehicle ID in the stop tab.

6. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

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.

7. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

Amazing work - thank you so much

8. ## Re: Flag up purchases made when the card was in a different location to associated vehicle

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)