Reconcile two columns
Closed
Banti
-
17 Feb 2011 à 10:19
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 18 Feb 2011 à 08:13
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 18 Feb 2011 à 08:13
Related:
- Reconcile two columns
- Tweetdeck larger columns - Guide
- How to delete columns in word - Guide
- Display two columns in data validation list but return only one - Guide
- Excel partial text match between two columns - Guide
- Excel columns changed to numbers - Guide
4 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
17 Feb 2011 à 10:45
17 Feb 2011 à 10:45
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 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
17 Feb 2011 à 15:38
17 Feb 2011 à 15:38
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 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
18 Feb 2011 à 08:13
18 Feb 2011 à 08:13
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
