+ Reply to Thread
Results 1 to 14 of 14

Vba code to match multiple debits and credits by account number

  1. #1
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Vba code to match multiple debits and credits by account number

    I've been using VBA in a limited capacity to help automate manual tedious jobs. However, I've run into something much more complex than my current abilities. I'm actually trying to eliminate hours of matching by building a macro that could possibly loop through the entire "Account #" list in Column "K", and be able to match multiple Debits (Column"F") against possibly multiple Credits (Column "E) and if there is a match, to highlight those matches (both column E and F) with a background color when a match occurs. I hope I explained this properly, but if you have questions, please feel free to let me know. I have attached a sample of my data. If something could be figured out, I could streamline this same VBA code against multiple accounts and save hours upon hours of time.
    I would really appreciate any information, direction, or help someone has to offer!
    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Vba code to match multiple debits and credits by account number

    are you hoping to highlight only if the change in balance for a given account = 0? i.e. all debits and credits perfectly offset?
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito

  3. #3
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    I think if there are any matches, they could be highlighted but not necessarily that all debits and credits need to perfectly match. In most cases they eventually will, but there can be delays in transactions posting that could cause "all" to not match. The best way to describe it is there will always be a little bit of a rolling forward effect and okay with trying to match all if that is the easiest way to set it up. I'm open to any ideas, and if it would make it easier, I could even combine the Debit/Credit columns for simplicity.

  4. #4
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    Once it does find matches and highlights them, I will be putting "today's date" in Column G, to show that those have cleared, so the macro should ignore any rows that are already highlighted or already have a date in Column G.

  5. #5
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Vba code to match multiple debits and credits by account number

    this seems to be solving the first version i described...

    Please Login or Register  to view this content.
    i think the latter is significantly more difficult - the $ amounts for these transactions aren't unique so i'm not really coming up with a way to tell vba which transactions are related, and how to ignore transactions which have already been tied to another... not to say it's impossible...

  6. #6
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    Wow......your fast! I think this could work just fine as it could eliminate the obvious quickly, then after a week or so, I may need to investigate the non matches but avoid spending lots of time sorting and manually highlighting. I really appreciate the super fast turnaround Simarui!

  7. #7
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Vba code to match multiple debits and credits by account number

    glad i could help. i'm still looking at the other request but it is pretty tricky... the added "cleared date" i think makes it a little bit easier, but i'm still having issues - will probably have to think about it overnight and check back in in the morning (if someone doesn't beat me to it)

  8. #8
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    You've already helped me plenty and I'm forever grateful! I am very impressed with this forum and the willingness for others to help! I'm always looking for ways to improve something, so for those that are super smart and love brain teasing challenges, I'm sure I'll have more
    The best part is someone may be looking for that same help, so solving one persons problem is actually helping solve hundreds to thousands of peoples problems.

  9. #9
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    I did just try the macro on 7000 rows of data and it did take about 3 minutes to run, which isn't bad. If there was a way we could ignore the rows that already have a cleared date in Column G, it may speed up the macro. If you do have any ideas on that piece, great, if not, it is still a lifesaver!

  10. #10
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: Vba code to match multiple debits and credits by account number

    this looks like it's working to me... creates the cleared date in column G, finds individual transactions to clear and colors them in.

    got kinda gnarly, had to borrow the findall() function from chip pearson (http://www.cpearson.com/excel/findall.aspx), but on your sample dataset of 884 records it looks like it's running in a little less than a second.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    Wowsers! That looks extremely complex, plus it looks like you spent a lot of time on it? I can't thank you enough for helping me out. I sure hope you are well compensated at your current employer for the amount of knowledge and expertise that you posses! I'll give this a try and see what happens and let you know!

  12. #12
    Registered User
    Join Date
    04-06-2015
    Location
    Sioux Falls, SD
    MS-Off Ver
    2013
    Posts
    14

    Re: Vba code to match multiple debits and credits by account number

    I tried it out and it works fabulous! I wish there was something I could do to return the favor as you just saved me a ton of manual and tedius steps! I owe ya big time!

  13. #13
    Registered User
    Join Date
    06-23-2015
    Location
    Poland
    MS-Off Ver
    2012
    Posts
    1

    Re: Vba code to match multiple debits and credits by account number

    Hi Simarui,

    Great work ! I have one question though - I wanted to use your code for my debit credit list but it doesn't account for values with decimal fractions (i.e. 602,12 and -602,12 would not be matched by your macro). Is there some way to adjust the code to include matching decimal values as well ?

    Thanks in advance

    Kind regards,

    AT

  14. #14
    Registered User
    Join Date
    07-12-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    2

    Re: Vba code to match multiple debits and credits by account number

    can someone help to write a vba code to automate outlook email attachment to a list of emails? Thanks

+ 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. [SOLVED] Lookup Account Name. Sum Debits and Credits to get Balance
    By NoExcelSkills17 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2015, 03:09 PM
  2. Debits and credits
    By Willow350 in forum Excel General
    Replies: 2
    Last Post: 12-07-2014, 11:34 AM
  3. Highlighting Debits and Credits
    By vharjani in forum Excel General
    Replies: 15
    Last Post: 02-23-2012, 02:20 PM
  4. Can't remove debits/credits that match
    By grecon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2010, 04:28 PM
  5. [SOLVED] Change debits to credits
    By Yo in forum Excel General
    Replies: 1
    Last Post: 04-10-2006, 08:20 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