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



## Harmandeep (Jan 5, 2023)

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 (Jan 5, 2023)

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 (Jan 6, 2023)

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 (Jan 6, 2023)

I am also attaching Sample file with expected result.


----------



## shinigamilight (Jan 6, 2023)

*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 (Jan 6, 2023)

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 (Jan 6, 2023)

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


----------



## Harmandeep (Jan 6, 2023)

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


----------



## shinigamilight (Jan 6, 2023)

Harmandeep said:


> 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 (Jan 6, 2023)

Pfa Error screen shot


----------



## Harmandeep (Jan 5, 2023)

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


----------



## shinigamilight (Jan 6, 2023)

Harmandeep said:


> Pfa Error screen shot


Show me the sheet where your data is.


----------

