+ Reply to Thread
Results 1 to 4 of 4

Search Bank Statement Lines for Partial Strings Stored in Reference Table

  1. #1
    Registered User
    Join Date
    03-30-2017
    Location
    Manchester, UK
    MS-Off Ver
    Windows 10
    Posts
    8

    Search Bank Statement Lines for Partial Strings Stored in Reference Table

    Hi All - newbie here so please be kind!

    I have a problem I am trying to solve in vba, but I need help as I'm not even sure how to do it in Excel using regular formulae so I can't even record on screen steps to get a clue!

    I have two Worksheets:
    1. cumulative bank statement with the following fields [date, line description, payment type, credit amount, debit amount]
    2. reference table containing unique partial strings from regularly occurring transactions and against each string a code

    What I want to do when a new bank statement is added, is perform a search of all the unique partial strings from my reference table within each of the new line description fields, and if there is a match, return the code from the reference table corresponding to unique partial string that was found. The idea being to speedily code the regular/recurring transactions (rent, rates, gas, elec, etc) and just leave the others that need manually reviewing and coding.

    I think this should be possible with some sort of array formula but I'm stumped, so all help gratefully received.

    This is an example of a couple of line descriptions:
    DIRECT DEBIT PAYMENT TO UNITED UTIL WATER REF ##########, MANDATE NO 0009
    INTEREST PAID AFTER TAX 0.00 DEDUCTED

    and extract from reference table:
    Unique String Code
    OVO ENERGY 22: Util_Gas/Elec/Water/Insur
    UNITED UTIL WATER 22: Util_Gas/Elec/Water/Insur
    HML T/A AVIVA 22: Util_Gas/Elec/Water/Insur
    Cashback 02: Interest/Fees
    INTEREST 02: Interest/Fees
    MONTHLY FEE 02: Interest/Fees
    EE LIMITED 23: Util_Mobiles

    So in the two line descriptions I want to put "22: Util_Gas/Elec/Water/Insur" against the first and "02: Interest/Fees" against the second.

    Hope that all makes sense.
    Last edited by Bob_W; 10-07-2020 at 12:13 PM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,082

    Re: Search Bank Statement Lines for Partial Strings Stored in Reference Table

    I'm not sure about a formula but a macro could what you want. If you are interested in a macro, it would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    03-30-2017
    Location
    Manchester, UK
    MS-Off Ver
    Windows 10
    Posts
    8

    Re: Search Bank Statement Lines for Partial Strings Stored in Reference Table

    Quote Originally Posted by Mumps1 View Post
    I'm not sure about a formula but a macro could what you want. If you are interested in a macro, it would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.
    Thanks for the reply Mumps1. I've managed to work out a solution now based on the suggested similar threads that appeared below. I've gone with a Lookup formula which does what i need and have embedded that in the code for my macro

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,082

    Re: Search Bank Statement Lines for Partial Strings Stored in Reference Table

    Glad it worked out.

+ 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. Vlookup Partial Text - Transactions from Bank Statement
    By jowettluke in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-27-2025, 10:57 PM
  2. How to search sub-strings in a lookup table
    By Fireball69 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2019, 11:00 AM
  3. [SOLVED] Running Total to Mimic BAnk Statement - Latest Entry at Top of Table
    By snapfade in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-11-2018, 03:34 AM
  4. Bank Statement Search Formula
    By dtweaponx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2016, 10:25 AM
  5. Formula/vba to search partial strings in other sheet
    By kevivu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-28-2015, 05:55 AM
  6. [SOLVED] Auto search bank statement for vendor and expenses
    By justr8hr in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 05-22-2013, 12:11 PM
  7. [SOLVED] Excel 2007 : Find bank balance in bank statement
    By Baldev Kumar in forum Excel General
    Replies: 5
    Last Post: 07-05-2012, 02:58 PM

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