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.
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 …
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
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.
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.
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 …
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.
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.
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.
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.
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.
Re: VBA: Reconciliation- Macros to propose matching values
Originally Posted by Marc L
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"
Re: VBA: Reconciliation- Macros to propose matching values
Originally Posted by SDB20
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.
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 ?
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.
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 :
Originally Posted by SDB20
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' ?!
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 B = "Break report.xlsx", S = "system report.xlsx" Dim V, Rg(3) As Range, L&, R&, T$(), C% For Each V In Array(B, S) If Not IsOpened(V) Then 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 » ! ◄ ◄
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."
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 …
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?
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?
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 B = "Break report.xlsx", S = "system report.xlsx" Dim V, M$(), Rg(3) As Range, W, L&, R&, T$(), C% For Each V In Array(B, S) If Not IsOpened(V) Then 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 » ! ◄ ◄
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.
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 ?
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.
In this second demonstration the currency criteria is removed, to paste in the module where is located Demo1 :
PHP Code:
Sub Demo2() Const B = "Break report.xlsx", S = "system report.xlsx" Dim V, M$(), Rg(3) As Range, W, L&, R&, T$(), C% For Each V In Array(B, S) If Not IsOpened(V) Then 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
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.
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
Bookmarks