+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    04-08-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    4

    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
    Attached Files Attached Files
    Last edited by ProblemSolver; 04-14-2020 at 06:13 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,949

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    04-08-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    4

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,949

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-08-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    4

    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.


    Thanks in advance for your assistance!!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,949

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-08-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    4

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

    Amazing work - thank you so much

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,949

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vehicle Time Table in Excel to Vehicle Movement Graph in Excel
    By Petrus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2019, 04:54 AM
  2. How to put the first three words to appear at random location on each bingo card?
    By maytam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2017, 07:58 AM
  3. Index vehicle, driver and vehicle wise revenue report.
    By dackson in forum Excel Formulas & Functions
    Replies: 41
    Last Post: 06-02-2017, 01:40 AM
  4. Calculating Card Surcharge, only when paying by card
    By OneScotty2Hotty in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-03-2016, 01:55 PM
  5. Replies: 2
    Last Post: 01-30-2013, 12:06 PM
  6. [SOLVED] Credit card type based on card number
    By wlsnoops in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-26-2013, 10:25 AM
  7. A function to split cash and credit card purchases.
    By DorothyFan1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-03-2011, 01:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1