+ Reply to Thread
Results 1 to 16 of 16

Macro to match Debits and Credits, verifying four columns of each debit and credit match

  1. #1
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Macro to match Debits and Credits, verifying four columns of each debit and credit match

    Hello,

    I'm working on a spreadsheet with close to 1 million lines. I need to match debits and credits in order to determine if incorrectly entered data lines have been fixed. There are some debit and credits that have 10 or twenty entries of the same amount so I need to only match the debit and credit amount that have matching 4 columns. Also, there are varying situations... in some instances there could be an 2 credits and 3 debits with all matching criteria, etc. I need to only highlight the debits and credits that sum to zero, that way I can determine which lines still need to be corrected.

    I borrowed a macro from on here and tried to adjust some of the code, but no luck! I've highlighted the columns that need to match and highlighted row 52 and 53 as an example of a debit and credit that I would be trying to identify. Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Since you probably import your csv file to excel before you try to determine incorrect data I would suggest you upload an excel file instead of a csv file and looking at the imported result of the uploaded csv file and reading your description of your problem I'm not sure you uploaded the right file.

    Alf

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    @Alf

    I couldn't find the relationships either...
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Hi Guys, the highlighting was lost on the csv upload. The "columns to compare" are M, G, F, and E, which is Account, Cost Center, Fund, and Function, respective. The numbers in the "columns to compare" all need to match each other, and the associated sum amounts (column L) need to be positive and negative and sum to zero.

    Basically, certain information on columns M,G,E,and F was incorrectly entered.
    Some corrections have already been made. When a correction is made there will be one line with all "columns to compare" cells matching exactly to another line except the sum amount (column L) will be opposite, ie -25 and 25. Once I am able to match these lines, I will be able to do one large correction on all the
    remaining lines. Thanks again. Any help is greatly appreciated!!

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Hi forcedto register

    Welcome to the Forum!!!

    A million Rows?
    • Where's this detail coming from?
    • Why is it so out of whack?
    • Out of the million Rows how many do you expect to match?

    Show us this...it may give us a clue...
    I borrowed a macro from on here and tried to adjust some of the code, but no luck!

  6. #6
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Maybe see how close this gets you to what you want?
    the x in the last column signifies matches of course.

    Of course can probably do better (faster) but jaslakes questions may effect what "better" is.

    Please Login or Register  to view this content.
    Last edited by scottiex; 09-07-2016 at 07:52 PM.

  7. #7
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Thanks Y'all,

    Jaslake, The information is coming from my organizations data system which is peoplesoft. The reason the data is off in some columns is that each department in our org inputs their own information and sometimes they put the wrong information in their journal entries. The first step is to reverse the entry and the next step is to enter with the correct information. Right now I'm only trying to determine which lines need to be corrected and which have already been corrected. Sometimes, they realize they made a mistake and correct it and other times they do not. We try to fix all these mistakes at fiscal year end so our numbers will be right.

    What I was doing was comparing each line to what it should be and if there was an error, the formula would show false. Then I'd use the formulas:
    =COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,L384)*COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,-L384)=1
    =COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,L384)*COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,-L384)=4
    =COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,L384)*COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,-L384)=9
    =COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,L384)*COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,-L384)=16
    =COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,L384)*COUNTIFS(D:D,D384,E:E,E384,F:F,F384,G:G,G384,M:M,M384,L:L,-L384)=25

    This would get me to the point where all the credits and debits that had a 1:1 match, 2:2 match, 3:3 match, 4:4 match and 5:5 match would be identified. This method still required me to check all the remaining rows manually because in some situations there is a 1:3 match, 1:6 match, or 7:7 etc.

    I'm expecting to need to make 4-5k corrections and have based on what the the above formulas calculated about 2k matches that have already been corrected by the departments previously.

    Scottiex, thank you! this works, but it's picking up one situation as a non match that should be a match. I'm uploading the list of matches I found by debit and credit. Your macro also identified two lines that should not be a match, but I had marked as a match-my mistake. I'm trying to get this right and me going through line by line is prone to mistakes and it would be very beneficial to check this on a regular basis. I only uploaded a small sample before of the 1m lines. This upload is all the lines I cut out of the main file that I marked as matched.

    Lines 384 and 784, I believe should offset each other based values, but are showing no x in column AE. I'm not at the point yet where I can understand the if else's very well.

    Thank you

    Thank you

  8. #8
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Data File 2
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    This was the macro, I was trying to use earlier. The "columns to compare" are different in this because I was trying to use Journal ID as a matching content. The "columns to match" which I mentioned in the earlier post will provide more accurate results (Dept ID, Function, Fund, Cost Center, and Account)

    Sub MatchColum3()

    Dim WkRg As Range
    Dim DispRg As Range
    Dim ColM(), ColG(), ColO(), ColL()
    Dim LastRow As Long
    Dim ObjDic As Object
    Set ObjDic = CreateObject("Scripting.Dictionary")
    Dim I As Long
    Dim TEMP
    Dim AAA, BBB
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlManual
    Columns("S:S").Interior.Pattern = xlNone
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set WkRg = Range("m2:m" & LastRow)
    ColM = WkRg
    Set WkRg = Range("g2:g" & LastRow)
    ColG = WkRg
    Set WkRg = Range("o2:o" & LastRow)
    ColO = WkRg
    Set WkRg = Range("l2:l" & LastRow)
    ColL = WkRg
    For I = 1 To LastRow - 1
    TEMP = ColM(I, 1) & ColG(I, 1) & ColO(I, 1) & ColL(I, 1)
    If (ObjDic.exists(TEMP)) Then
    ObjDic.Item(TEMP) = ObjDic.Item(TEMP) + 1
    Else
    ObjDic.Item(TEMP) = 1
    End If
    Next I
    Set DispRg = Range("S1")
    For I = 1 To LastRow - 1
    TEMP = ColM(I, 1) & ColG(I, 1) & ColO(I, 1) & -1 * ColL(I, 1)
    If (ObjDic.exists(TEMP)) Then
    If (ObjDic.Item(TEMP) > 0) Then
    ObjDic.Item(TEMP) = ObjDic.Item(TEMP) - 1
    Set DispRg = Union(DispRg, Range("S" & I + 1))
    End If
    End If
    Next I
    DispRg.Interior.Color = 5296274
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    End Sub
    Last edited by forcedto register; 09-09-2016 at 06:22 PM.

  10. #10
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Quote Originally Posted by forcedto register View Post
    Lines 384 and 784, I believe should offset each other based values, but are showing no x in column AE. I'm not at the point yet where I can understand the if else's very well.
    No those two have different accounts. Are you saying the account is a typo?

    If you want that fixed you'll need to give us more examples and preferably a logical rule to know when what is apparently a match is not actually a match etc.

    BTW you need to put your code in code tags like I do. the tags are like this [ CODE ] [ /CODE ]
    Last edited by scottiex; 09-09-2016 at 06:59 PM.

  11. #11
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Hi forcedto register

    I can't imagine why anyone would wait until fiscal year end to fix this.
    We try to fix all these mistakes at fiscal year end so our numbers will be right
    .
    I can suggest an approach for this but I'll not suggest an approach for a million records. My approach will not be workable for that large of a file...it'll run until your next fiscal year end. Best advice...fix them as they happen.

    Sorry.

  12. #12
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    Scottiex, my mistake, you are right...ok, I'm ran the macro on another list of about 6k lines (im uploading). On line 6243 which is amount 97.45, It's showing a match but there is no negative amount to offset. Also, can we add Dept ID (column D) to the "columns to compare"?

    To your question Jaslake, once we have a good way to run through these quickly we can do this more often, but right now, it's been going through things without automation so by the time we come up with a list, someone from another dept has possibly already corrected the mistake.

    Hey Guys I appreciate your help.

    Peter
    Attached Files Attached Files
    Last edited by forcedto register; 09-14-2016 at 10:24 AM.

  13. #13
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    OK

    Please Login or Register  to view this content.
    Last edited by scottiex; 09-15-2016 at 06:14 PM.

  14. #14
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    This one stops after the first line

  15. #15
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    really? It works for me.
    which line you don mean by the first line ?
    lastrowx = lastrow ?
    maybe you have option explicit on and should go
    Please Login or Register  to view this content.
    first.
    I'll add it

  16. #16
    Registered User
    Join Date
    09-01-2016
    Location
    mars
    MS-Off Ver
    99
    Posts
    8

    Re: Macro to match Debits and Credits, verifying four columns of each debit and credit mat

    One of the columns was off from the the file I uploaded. This macro works. Thank you so much. HuUUge help!!!

+ 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] match identical debit and credit amounts
    By kris26 in forum Excel General
    Replies: 17
    Last Post: 12-04-2019, 04:30 AM
  2. [SOLVED] VBA Macro to format credits and debits.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-04-2016, 12:51 PM
  3. Want to Match Debits and Credits for la ist of Customers
    By Suri159951 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2016, 10:30 AM
  4. way to match 2 or amounts to 1 amount, debits and credits!
    By nimv1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2016, 08:57 PM
  5. consolidating columns debits and credits
    By jeck876 in forum Excel General
    Replies: 1
    Last Post: 10-02-2015, 05:37 PM
  6. [SOLVED] Vba code to match multiple debits and credits by account number
    By TheRock_82 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-12-2015, 05:17 PM
  7. 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

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