Reconcile two columns

Closed
Banti - Feb 17, 2011 at 10:19 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Feb 18, 2011 at 08:13 AM
Sir I m a banker I have to reconcile the entries weekly. Suppose i have two columns A & B like this:
Col-A Col-B
3456 789
2000 3000
789 3456 and so on... I would like to get unreconciled entries after running macro like this
Col-C Col-D
2000 3000

It is so urgent becoz Financial closing day is very closed.
plz guide step by step

Gunjan Kumar Banti
Central Bank of India
India

4 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Feb 17, 2011 at 10:45 AM
Hi Banti,

Try this in C2 and then drag across column C and D:
=IF(CEILING(A2,1000)=A2,A2,"")

Best regards,
Trowa
0
Its not perfect
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 17, 2011 at 03:38 PM
Try this
Public Sub doReconcile()

   Dim objRows             As Object
   Dim objRecon            As Object
   Dim lRow                As Long
   Dim sKey                As String
   Dim sReconKey           As String
   Dim lItem               As Long
   Dim vUnrecon            As Variant
   Dim vPos                As Variant
   
   Set objRows = CreateObject("Scripting.Dictionary")
   Set objRecon = CreateObject("Scripting.Dictionary")
   
   lRow = 1
   
   Do While ((Cells(lRow, "A") <> vbNullString) _
         Or (Cells(lRow, "B") <> vbNullString))
   
      sKey = Cells(lRow, "A") & "|" & Cells(lRow, "B")
      sReconKey = Cells(lRow, "B") & "|" & Cells(lRow, "A")
      
      
      If (objRecon.Exists(sKey)) _
      Then
         objRows.remove (CStr(sReconKey & " - " & objRecon(sKey)))
         Select Case objRecon(sKey)
            Case Is = 1
               objRecon.remove sKey
            Case Else
               objRecon(sKey) = objRecon(sKey) - 1
         End Select
      ElseIf objRecon.Exists(sReconKey) _
      Then
         objRecon(sReconKey) = objRecon(sReconKey) + 1
         objRows.Add Key:=CStr(sKey & " - " & objRecon(sReconKey)), Item:=lRow
      Else
         objRecon.Add Key:=sReconKey, Item:=1
         objRows.Add Key:=CStr(sKey & " - " & objRecon(sReconKey)), Item:=lRow
      End If
         
      lRow = lRow + 1
   Loop
   
   lRow = 0
   For Each vUnrecon In objRecon
      vPos = InStr(1, CStr(vUnrecon), "|")
      sKey = Left(CStr(vUnrecon), vPos - 1)
      sReconKey = Mid(CStr(vUnrecon), vPos + 1)
      For lItem = 1 To objRecon(CStr(vUnrecon))
         lRow = lRow + 1
         Cells(lRow, "C") = sReconKey
         Cells(lRow, "D") = sKey
      Next
   Next
   
   objRows.removeAll
   Set objRows = Nothing
   
   objRecon.removeAll
   Set objRecon = Nothing
   
End Sub
0
Sir can u guide me step by step how to do this? I use ofiice 2010 plz.....
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 18, 2011 at 08:13 AM
1. open excel
2. open the file
3. goto the sheet that you want to work on
4. press keys ALT and F11 at the same time to open the visual basic editor or VBE
5. In the VBE, click on Insert and insert a new module
6. copy all the code from lines
Public Sub doReconcile()
till line
End sub
(both line including)
7. press F5 to run the macro
8. see the results back in the sheet
0