Reconcile two columns
Closed
Banti
-
Feb 17, 2011 at 10:19 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 18, 2011 at 08:13 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 18, 2011 at 08:13 AM
Related:
- Reconcile two columns
- Display two columns in data validation list but return only one - Guide
- Tweetdeck larger columns - Guide
- How to delete columns in word - Guide
- Excel partial text match between two columns - Guide
- How to combine debit and credit columns in excel ✓ - Excel Forum
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 17, 2011 at 10:45 AM
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
Try this in C2 and then drag across column C and D:
=IF(CEILING(A2,1000)=A2,A2,"")
Best regards,
Trowa
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 17, 2011 at 03:38 PM
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
Sir can u guide me step by step how to do this? I use ofiice 2010 plz.....
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 18, 2011 at 08:13 AM
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
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
