Bank Recon where date and amount is common & Bank provide single entry for bulk transaction & in books we record in each entry individually

[Harmandeep]

Dear Excel Experts,

I am also stuck on the same situation. I have to do bank recon, where bank gives combined payment entry for particular Date & in our books we records as individual entry.E.g on Date 12/12/2022 Bank Cr Amount 10,000 with single transaction & in our books we have recorded Dr Amount 10,000 with 10 transaction. I have to matched individually with diff promotion combination adding diff amount.

If anybody have any solution for this, Pls share. It would be great help.


Regards,
Harmandeep Singh

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Bank Recon where date and amount is common & Bank provide single entry for bulk transaction & in books we record in each entry individually
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Harmandeep]

Hi Peter

Pfb the link of other fourm where this question is posted.

Bank Recon where date and amount is common & Bank provide single entry for bulk transaction & in books we record in each entry individually

Dear Excel Experts, I am also stuck on the same situation. I have to do bank recon, where bank gives combined payment entry for particular Date & in our books we records as individual entry.E.g on Date 12/12/2022 Bank Cr Amount 10,000 with single transaction & in our books we have recorded Dr...

chandoo.org

Regards,
Harmandeep Singh

 

[Harmandeep]

I am also attaching Sample file with expected result.

 

[shinigamilight]

I cannot guarantee that it will work, I'm not responsible for any data loss.


ENABLE MICROSOFT SCRIPTING RUNTIME OR THIS WILL NOT WORK

Make sure all your dates are legit dates cuz from the screenshot it looks like some of them are text.

Sub Recon()
        Dim v As Variant
        Dim dic As New Scripting.Dictionary
        Dim lr, lr2 As Long
        Dim k, j, P As Integer
        lr1 = Range("A" & Rows.Count).End(xlUp).Row
        lr2 = Range("J" & Rows.Count).End(xlUp).Row
        Dim store As Date
        Dim CreditValue As Double
        Dim InterStorage As Double
       
        For k = 2 To lr1    ' 2  is the row number where the data collection will be started,change it accordingly
                store = Range("A" & k)
                CreditValue = Range("D" & k)
                For j = 2 To lr2 ' 2  is the row number where the data collection will be started,change it accordingly
                        If Range("I" & j).Value = store Then
                            InterStorage = InterStorage + Range("J" & j)
                            dic.Add store & P, j
                            P = P + 1
                         End If
                Next j
                        If InterStorage = CreditValue Then
                        For Each v In dic.Items
                                    Range("K" & v) = "Matched"
                        Next v
                        Range("E" & k) = "Matched"
                        Else: Range("E" & k) = "Not Matched"
                        For Each v In dic.Items
                                  Range("K" & v) = "Not Matched"
                        Next v
                        End If
        InterStorage = 0
        dic.RemoveAll
        P = 0
        Next k
       
       
       

End Sub

 

[Harmandeep]

Hi Shinigamilight,

Thanks for replying. It gives error on (Dim dic As New Scripting.Dictionary) if i remove this from code then its stuck on store = Range("A" & k).

 

[shinigamilight]

Don't remove that line you just need to enable this in the references

 

[Harmandeep]

I tried with enabling Microsoft Script Runtime but its Shows me error at (store = Range("A" & k)).

 

[shinigamilight]

I tried with enabling Microsoft Script Runtime but its Shows me error at (store = Range("A" & k)).

Post a screenshot of your data showing everything clearly. And what error is it throwing up.

 

[Harmandeep]

Pfa Error screen shot