+ Reply to Thread
Results 1 to 39 of 39

VBA: Reconciliation- Macros to propose matching values

  1. #1
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Post VBA: Reconciliation- Macros to propose matching values

    Hello All,

    Hope you are doing well.

    Its part of the job to reconcile a large list of transactions from the provided two files:

    1. Break report
    2. system report

    I believe instead of manual reconciling this activity can be done using macros. the idea is to tentatively match the fee payments expected against the payments received.If we identify a potential match, highlight the fee or row so we can mark it"received" in our system and process the entry.

    in the system report we have:
    - column R,S,T: the expected payments (depending on currency)
    - columns A the invoice ref
    - column E and F ,client name and bank name

    in the break report, we have:
    - Column E , we limit our scope to "L CR & S CR" for payment received.
    - Column F,G :curreny and amount
    - column I , ref comments where we can identify name and reference if lucky the invoice # provided

    The tricky part here is to deal with round amount (examples 5,000.00 USD; 10,000.00 HKD). If we spot a potential match based on currency and amount,we need to identify the remitter matches or if the comment contain part of the deal name of the invoice #.

    The target is to:
    1.Get these 2 reports generated and downloaded from systems.
    2.Run the built-in Macro to propose matching.
    3. Operator to review the proposed matching.

    Please see attached file for reference which has minimal data and thank you so much.
    Attached Files Attached Files
    Last edited by SDB20; 06-01-2021 at 10:45 AM.

  2. #2
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation project

    Appreciate it , if someone can suggest or help here.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    Hi,

    it's weird to post an attachment which not matches your explanation
    as there is nothing to identify the name or the account so any correct reconciliation can be done.
    Smart worksheets must be with an additional column for an account reference for example.
    As there are reconciliation samples in Excel forums just performing a web search

  4. #4
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Hi Marc- thank you for your response.

    Just to be sure, I have removed and re-attached the excel files.
    when you say"attachment not matches my explanation", could you please advise which section.
    As you rightly identified that we do not have account numbers so we have to find the closest/partial match based on the invoice# (contains word CTLA/CLTA) and the amount under CCY column, in some scenario clients name will be available -then in that condition we to need match with clients name(partial name also will help) and amount as mentioned on the excel sheets as HCL, Apple,Suzuki etc.

    In the worst-case scenario, if the above is impossible then a duplicate finder between 2 excel workbook based on Inv # and amount might help too.
    Hope the above clarifies

  5. #5
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Hi Marc- thank you for your response.

    Just to be sure, I have removed and re-attached the excel files.
    when you say"attachment not matches my explanation", could you please advise which section.
    As you rightly identified that we do not have account numbers so we have to find the closest/partial match based on the invoice# (contains word CTLA/CLTA) and the amount under CCY column, in some scenario clients name will be available -then in that condition we to need match with clients name(partial name also will help) and amount as mentioned on the excel sheets as HCL, Apple,Suzuki etc.

    Hope the above clarifies

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    Attach sample workbooks covering at least all possible scenarios and well elaborate each one according to their 'matching' rows

  7. #7
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Thumbs up Re: VBA: Reconciliation- Macros to propose matching values

    Thanks, Marc, my apologies as I noticed few details were not right on the earlier spreadsheet as pointed. I now have attached samples highlighting matching rows and comments under column U with 'match' ,'Partial match' OR 'no match' in "system report" excel sheet. Below is detailed description of match,partial match and no match values:

    1. Row2 of system report(Not Highlighted): No match as no details of this row exist in break report extract.

    2. Row3 of system report (Highlighted in Green): Match, based on INV # located under cell I2 ,amount under cell G2 and client name under cell A4 on break report.

    3. Row4 of system report (Highlighted in Blue): Partial Match,based on client name under cell A3 as Apple INC vs Apple in system report ,amount under cell G3 and partial invoice # under cell I3 of break report.

    4. Row4 of system report (Highlighted in Light Red): Match, based on client name under cell A4 and amount under cell G4 of break report.

    5. Row5 of break report (Not Highlighted): No match as no details of this row exist in system report extract.(vice-versa).

    Hope the above is not too confusing.

    Also ,if this is way too complex we can look at , match with combinations and permitation of two criterias such as :

    1) Last 5 digits of invoice number in system report (column A) and client name or amount.

    OR
    2) With Partial client name and Amount.

    OR
    3)Complete Invoice number and amount.

    OR
    4)With just partial match with client name.

    the actual file has up to 2000 rows and thank you for looking into this.
    Attached Files Attached Files
    Last edited by SDB20; 06-01-2021 at 10:40 AM.

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Question Re: VBA: Reconciliation- Macros to propose matching values


    Where should be located the VBA procedure : in one of your last attachment or in a third workbook ?

  9. #9
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    A third workbook will be excellent.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    Well check your post #7 about each criteria as if I post some VBA demonstration it should be a one-shot-code
    meaning I won't modify it for anyrhing you have forgotten to explain
    not a big deal if you are enough confident with your Excel / VBA skills in order to fit yourself any code

  11. #11
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Yes Marc, I don't think I have missed anything to explain,as you mentioned small modification I will be able to do it myself if any required later on.
    just to add from my post#1 that in the break report, we have:
    - Column E , we limit our scope to "L CR & S CR" . If this creates an issue then we can ignore it and can be filtered manually.
    Last edited by SDB20; 06-01-2021 at 12:09 PM.

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,428

    Re: VBA: Reconciliation- Macros to propose matching values

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
    Rory
    I drink, and I know things

  13. #13
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Hi Rory,

    Thank you for your message.
    I tried to post the cross-link however I received the below message:

    "The following errors occurred with your submission

    You are not allowed to post any kinds of links, images or videos until you post a few times."

    Just for reference the above post is also mentioned on ozgrid forum and link is provided for excelforum post.(Mentioned as per Forum rule # 3)
    Hope this helps.

  14. #14
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Hi Marc - just catching up with the forum, I understand you must be occupied with other stuff and I just wanted to kill my curiosity the VBA solution for this one will make our life a little easy.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Re: VBA: Reconciliation- Macros to propose matching values


    I'm a bit surprised any moderator answered to your post #13

    As you were catched by this forum moderators police so any solution can be posted
    without the link or the address of each thread about the same subject created on other forums.

  16. #16
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Quote Originally Posted by Marc L View Post

    I'm a bit surprised any moderator answered to your post #13

    As you were catched by this forum moderators police so any solution can be posted
    without the link or the address of each thread about the same subject created on other forums.
    "okay, should i retry posting the link on post#13,I am bit confused now"

  17. #17
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Quote Originally Posted by SDB20 View Post
    Hi Rory,

    Thank you for your message.
    I tried to post the cross-link however I received the below message:

    "The following errors occurred with your submission

    You are not allowed to post any kinds of links, images or videos until you post a few times."

    Just for reference the above post is also mentioned on ozgrid forum and link is provided for excelforum post.(Mentioned as per Forum rule # 3)
    Hope this helps.
    "https://www.ozgrid.com/forum/index.php?thread/1229728-vba-macros-for-reconciliation-matching-values-between-two-workbook/"

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Question Re: VBA: Reconciliation- Macros to propose matching values


    As the VBA procedure should be located in a third workbook,
    should the data workbooks be already opened or the VBA procedure can open each one if not opened ?

  19. #19
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    I assume it will be easy if we keep both the workbooks open and run the code or if we can click on the Marco and it gives us the option to select the workbooks from the target folder.

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Question Re: VBA: Reconciliation- Macros to propose matching values


    As the code workbook should be saved in the 'target folder' so once opened this folder is already known
    so the VBA procedure just needs to check if workbooks are opened thus meaning the workbooks names never change

    Another point :

    Quote Originally Posted by SDB20 View Post
    in the break report, we have:
    - Column E , we limit our scope to "L CR & S CR"
    So explain why row #4 is highlighted with 'L DR' ?!

  21. #21
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    yes, the workbook name remains "Break report & system report".
    Oops, apologies that were a typo as I typed 'L CR' and 'S CR' manually...

  22. #22
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Cool Try this !


    According to your attachment a VBA demonstration as a beginner starter :

    PHP Code: 
    Function IsOpened(BOOK) As Boolean
             On Error Resume Next
             IsOpened 
    IsObject(Workbooks(BOOK))
    End Function

    Sub Demo1()
           Const 
    "Break report.xlsx""system report.xlsx"
             
    Dim VRg(3) As RangeL&, R&, T$(), C%
        For 
    Each V In Array(BS)
            If 
    Not IsOpened(VThen
                V 
    ThisWorkbook.Path "\" & V:  If Dir(V) = "" Then Beep: Exit Sub
                Workbooks.Open V, False
            End If
        Next
            Set Rg(0) = Workbooks(B).Worksheets(1).[A1].CurrentRegion.Columns
            Application.ScreenUpdating = False
            Rg(0).Interior.ColorIndex = xlNone
        With Workbooks(S).Worksheets(1).[A1].CurrentRegion.Rows
               .Interior.ColorIndex = xlNone
            For L = 2 To .Count
                   Set Rg(1) = .Item(L).Columns("
    R:T").Find("*")
                If Not Rg(1) Is Nothing Then
                       Set Rg(2) = Rg(0).Item(7).Find(Rg(1).Value2, , , xlWhole)
                    If Not Rg(2) Is Nothing Then
                           R = Rg(2).Row
                        Do
                            If Rg(2).Interior.ColorIndex = xlNone Then
                                If Rg(2)(1, -1).Text Like "
    CR*" And Rg(2)(1, 0).Text = .Cells(L, 9).Text Then
                                        Set Rg(3) = Rg(2)(1, 3).Resize(, 3)
                                        V = Application.Match(.Cells(L, 1).Text & "
    *", Rg(3), 0)
                                    If IsNumeric(V) Then
                                        C = 43
                                    Else
                                        C = 44
                                        T = Split(.Cells(L, 1).Text, "
    /")
                                        V = Application.Match("
    *" & T(UBound(T)), Rg(3), 0)
                                    End If
                                    If IsNumeric(V) Then
                                        Union(.Cells(L, 1), .Cells(L, 9), Rg(1)).Interior.ColorIndex = C
                                        Union(Rg(2)(1, -1).Resize(, 3), Rg(3)(1, V)).Interior.ColorIndex = C
                                        Exit Do
                                    ElseIf InStr(Rg(2)(1, -5).Text, .Cells(L, 5).Text) Then
                                        Union(.Cells(L, 5), .Cells(L, 9), Rg(1)).Interior.ColorIndex = C
                                        Union(Rg(2)(1, -5), Rg(2)(1, -1).Resize(, 3)).Interior.ColorIndex = C
                                        Exit Do
                                    End If
                                End If
                            End If
                               Set Rg(2) = Rg(0).Item(7).FindNext(Rg(2))
                        Loop Until Rg(2).Row = R
                    End If
                End If
            Next
        End With
            Application.ScreenUpdating = True
            Erase Rg
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 06-04-2021 at 12:36 PM.

  23. #23
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: Try this !

    Thank you so much Marc, really appreciate the quick help . seems to work great with sample worksheets.
    Just a couple of question if you do not mind, please
    - The VBA looks for match/partial match in the entire workbook or it has a limitation on rows or colums?
    - The provided code is written as per my post #7-row examples or with combinations and permutation of two criteria.

    A request - is it possible for you to explain the code for my understanding and reference
    I will run the provided VBA solution on the actual spreadsheets and come back. Thank you again

    and to add reputation, I receive the following message "You must spread some Reputation around before giving it to Marc L again."
    Last edited by SDB20; 06-04-2021 at 01:25 PM.

  24. #24
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    Thanks for the rep

    As you can see within the procedure there is no rows limitation

    Each colored cell is a criteria.
    - Green : match found according to the invoice #.
    - Orange : partial match according to the last part of the invoice # or according to the name

  25. #25
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Thanks Marc.

    I noticed that in the actual break report few columns were hidden and I missed updating those in the provided sample break report (my bad ), due to this I guess the VBA is not finding any match values. Do I need to update any columns numbers in the VBA?

  26. #26
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    Maybe yes, maybe no, as it depends on each column index

  27. #27
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    okay I tried changing the index columns numbers, and looks like I was not successful , may I request you to please help..
    I have updated the break report columns which were hidden in the actual sheet...Apologies for the trouble

    any clue why the VBA deletes the break report headers after running the code?
    Attached Files Attached Files

  28. #28
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Question Re: VBA: Reconciliation- Macros to propose matching values


    In new Break report workbook cell J6 : is it possible currency in lower case or is it again a typo ?

  29. #29
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    in some instances, we do have currency in a lower case under column J.

  30. #30
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Lightbulb Try this


    According to your last attachment the VBA demonstration revamped to paste to the top of a module :

    PHP Code: 
    Option Compare Text

    Function IsOpened(BOOK) As Boolean
             On Error Resume Next
             IsOpened 
    IsObject(Workbooks(BOOK))
    End Function

    Sub Demo1()
           Const 
    "Break report.xlsx""system report.xlsx"
             
    Dim VM$(), Rg(3) As RangeWL&, R&, T$(), C%
        For 
    Each V In Array(BS)
            If 
    Not IsOpened(VThen
                V 
    ThisWorkbook.Path "\" & V:  If Dir(V) = "" Then Beep: Exit Sub
                Workbooks.Open V, False
            End If
        Next
            M = Split("
    Match.Partial Match", ".")
            Set Rg(0) = Workbooks(B).Worksheets(1).[A1].CurrentRegion.Columns
            Application.ScreenUpdating = False
        With Workbooks(S).Worksheets(1).[A1].CurrentRegion.Rows
                W = .Columns(21).Value2
            For L = 2 To .Count
                   Set Rg(1) = .Item(L).Columns("
    R:T").Find("*")
                If Not Rg(1) Is Nothing Then
                       Set Rg(2) = Rg(0).Item(13).Find(Rg(1).Value2, , , xlWhole)
                    If Not Rg(2) Is Nothing Then
                           R = Rg(2).Row
                        Do
                            If Rg(2).Interior.ColorIndex = xlNone Then
                                If Rg(2)(1, -4).Text Like "
    CR*" And Rg(2)(1, -2).Text = .Cells(L, 9).Text Then
                                        Set Rg(3) = Rg(2)(1, 2).Resize(, 6)
                                        V = Application.Match(.Cells(L, 1).Text & "
    *", Rg(3), 0)
                                    If IsNumeric(V) Then
                                        C = 43
                                    Else
                                        C = 44
                                        T = Split(.Cells(L, 1).Text, "
    /")
                                        V = Application.Match("
    *" & T(UBound(T)), Rg(3), 0)
                                    End If
                                    If IsNumeric(V) Then
                                        W(L, 1) = M(C - 43)
                                        Union(.Cells(L, 1), .Cells(L, 9), Rg(1)).Interior.ColorIndex = C
                                        Union(Rg(2)(1, -4), Rg(2)(1, -2), Rg(2), Rg(3)(1, V)).Interior.ColorIndex = C
                                        Exit Do
                                    ElseIf InStr(Rg(2)(1, -11).Text, .Cells(L, 5).Text) Then
                                        W(L, 1) = M(1)
                                        Union(.Cells(L, 5), .Cells(L, 9), Rg(1)).Interior.ColorIndex = C
                                        Union(Rg(2)(1, -11), Rg(2)(1, -4), Rg(2)(1, -2), Rg(2)).Interior.ColorIndex = C
                                        Exit Do
                                    End If
                                End If
                            End If
                               Set Rg(2) = Rg(0).Item(13).FindNext(Rg(2))
                        Loop Until Rg(2).Row = R
                    End If
                End If
            Next
                .Columns(21).Value2 = W
        End With
            Application.ScreenUpdating = True
            Erase Rg
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  31. #31
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Thank you Marc for the amended code.

    One thing I observed that the code doesn't find any match just based on the invoice # or the last 5 digits of the invoice number.The CCY criteria on "system report" under column I seems to be mandatory.(example row # 9 on system report against row # 8 of break report)
    If you remove the CCY name under "column I" of system report (attached) ,the code doesn't show the exact or partial match. can we incorporate this one last thing please. very rare but at times we have noticed that few CCY is not mentioned under certain reports.
    Attached Files Attached Files
    Last edited by SDB20; 06-07-2021 at 08:36 AM.

  32. #32
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Question Re: VBA: Reconciliation- Macros to propose matching values


    As it could be a bad idea 'cause if you have several identical amounts with differents currencies it can be a mess
    or you are very sure to not check the currency anymore so in this case I will delete this criteria for all the process ?

  33. #33
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    can we not keep one criteria as a match or partial match based on just the invoice numbers? The reason I am asking this is because at times we do not have CCY names mentioned for few rows on system report. I admit that the system we have to extract these reports is pretty screwed up.

  34. #34
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Cool Last try


    In this second demonstration the currency criteria is removed, to paste in the module where is located Demo1 :

    PHP Code: 
    Sub Demo2()
           Const 
    "Break report.xlsx""system report.xlsx"
             
    Dim VM$(), Rg(3) As RangeWL&, R&, T$(), C%
        For 
    Each V In Array(BS)
            If 
    Not IsOpened(VThen
                V 
    ThisWorkbook.Path "\" & V:  If Dir(V) = "" Then Beep: Exit Sub
                Workbooks.Open V, False
            End If
        Next
            M = Split("
    Match.Partial Match", ".")
            Set Rg(0) = Workbooks(B).Worksheets(1).[A1].CurrentRegion.Columns
            Application.ScreenUpdating = False
        With Workbooks(S).Worksheets(1).[A1].CurrentRegion.Rows
                W = .Columns(21).Value2
            For L = 2 To .Count
                   Set Rg(1) = .Item(L).Columns("
    R:T").Find("*")
                If Not Rg(1) Is Nothing Then
                       Set Rg(2) = Rg(0).Item(13).Find(Rg(1).Value2, , , xlWhole)
                    If Not Rg(2) Is Nothing Then
                           R = Rg(2).Row
                        Do
                            If Rg(2).Interior.ColorIndex = xlNone And Rg(2)(1, -4).Text Like "
    CR*" Then
                                    Set Rg(3) = Rg(2)(1, 2).Resize(, 6)
                                    V = Application.Match(.Cells(L, 1).Text & "
    *", Rg(3), 0)
                                If IsNumeric(V) Then
                                    C = 43
                                Else
                                    C = 44
                                    T = Split(.Cells(L, 1).Text, "
    /")
                                    V = Application.Match("
    *" & T(UBound(T)), Rg(3), 0)
                                End If
                                If IsNumeric(V) Then
                                    W(L, 1) = M(C - 43)
                                    Union(.Cells(L, 1), Rg(1)).Interior.ColorIndex = C
                                    Union(Rg(2)(1, -4), Rg(2), Rg(3)(1, V)).Interior.ColorIndex = C
                                    Exit Do
                                ElseIf InStr(Rg(2)(1, -11).Text, .Cells(L, 5).Text) Then
                                    W(L, 1) = M(1)
                                    Union(.Cells(L, 5), Rg(1)).Interior.ColorIndex = C
                                    Union(Rg(2)(1, -11), Rg(2)(1, -4), Rg(2)).Interior.ColorIndex = C
                                    Exit Do
                                End If
                            End If
                               Set Rg(2) = Rg(0).Item(13).FindNext(Rg(2))
                        Loop Until Rg(2).Row = R
                    End If
                End If
            Next
                .Columns(21).Value2 = W
        End With
            Application.ScreenUpdating = True
            Erase Rg
    End Sub 

  35. #35
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: Last try

    okay right , so the code is now checking for match based on the following:

    - Green : match found according to the complete invoice # across sheets?
    - Orange : partial match according to the last part (last 5 digits) of the invoice # or according to the name across sheets?

    I guess then in this we need to add one more condition as both conditions have invoice # (match or partial) as unique values and we have instances where no invoice # is provided on break report to match against column A of system report.

    The 3rd should be :

    -Blue : Match/partial match according to the name (column E:system report) and amount.
    Last edited by SDB20; 06-07-2021 at 10:16 AM.

  36. #36
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    As yet stated in post #24 it can be only a partial match according to the name but you can modify the code if really necessary

  37. #37
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    The partial name match does not seem to work, if you look at the below snippet the "system report" has row 9 as John Deree LTD
    and "break report" row 8 has name mentioned as John Deree ..but the cells don't get highlighted

    System report

    Untitled-system report.png


    break report

    Untitled-break report.png

    Can we pls make this a "LAST TRY" and close , I do acknowledge your efforts here and really appreciate it, can't thank you enough
    Attached Files Attached Files
    Last edited by SDB20; 06-07-2021 at 01:15 PM.

  38. #38
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,824

    Arrow Re: VBA: Reconciliation- Macros to propose matching values


    First, 'LTD' is missing as the procedure compare the entire cell from system workbook.

    And the name is searched only in column A so you can add a search for the 'Ref' columns

  39. #39
    Registered User
    Join Date
    05-31-2021
    Location
    Bangalore
    MS-Off Ver
    2016
    Posts
    22

    Re: VBA: Reconciliation- Macros to propose matching values

    Great.Thank you!! I shall try the above.

+ 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. Replies: 3
    Last Post: 08-11-2017, 03:10 AM
  2. Replies: 1
    Last Post: 06-12-2017, 09:20 PM
  3. Replies: 5
    Last Post: 08-06-2014, 01:10 PM
  4. Replies: 1
    Last Post: 05-02-2014, 03:05 PM
  5. [SOLVED] Function that concatenates multiple sub-project descriptions based on project code
    By markbpi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 06:49 PM
  6. [SOLVED] Insert Blank Rows between sorted projects, Subtotal project to the right of final project.
    By ZAC7 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2012, 04:08 AM
  7. Replies: 1
    Last Post: 10-18-2005, 11:05 AM

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