Help to check balance account number

S

sbv1986

Board Regular
Joined
Nov 2, 2017
Messages
87
Hi all
I have file data.xlsm with 03 sheets("result","balance01","balance02")
Now I want macro VBA to check value each account number in row i sheet(balance01).column(B,C) with the same account number in row j sheets(balance02).column(F,G)
If the same account but value(Bi) <> value (Fj) or value(Ci) <> value (Gj) => copy row have diffrience value to sheets(result)
Please help me to do this

sheets(balance01) like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Acc number[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Increase[/TD]
[TD]Decrese[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10101[/TD]
[TD]1000[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10102[/TD]
[TD]2000[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10103[/TD]
[TD]3000[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10104[/TD]
[TD]3000[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












sheets(balance02) like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]V[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Acc number[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Increase[/TD]
[TD]Decrese[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10102[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2000[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10103[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3000[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3001[/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













sheets(result) like this:
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Acc number[/TD]
[TD]Debit balance01[/TD]
[TD]Creadit balance01[/TD]
[TD]Debit balance 02[/TD]
[TD]Credit balance02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10101[/TD]
[TD]1000[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10104[/TD]
[TD]3000[/TD]
[TD]300[/TD]
[TD]3001[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try:
Code: 
Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, desWS As Worksheet
    Set desWS = Sheets("result")
    Set ws1 = Sheets("balance01")
    Set ws2 = Sheets("balance02")
    Dim i As Long, v1 As Variant, v2 As Variant
    v1 = ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    v2 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Resize(, 7).Value
    Application.ScreenUpdating = False
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v1, 1)
            Val = v1(i, 1) & "|" & v1(i, 2) & "|" & v1(i, 3)
            If Not .Exists(Val) Then
                .Add Val, Nothing
            End If
        Next i
        For i = 1 To UBound(v2, 1)
            Val = v2(i, 1) & "|" & v2(i, 6) & "|" & v2(i, 7)
            If Not .Exists(Val) Then
                desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 3) = Array(v1(i, 1), v1(i, 2), v1(i, 3))
                desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1, 0).Resize(, 2) = Array(v2(i, 6), v2(i, 7))
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks mumps, code work well. I Have a small question is:
How to change code if I copy data balance 02 to the same sheet?. This mean balance01 from column(A:G) an balance02 fromm column(I:0)
sheet(balance01) look like:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Acc[/TD]
[TD]Deb[/TD]
[TD]Cre[/TD]
[TD]Inc[/TD]
[TD]Dec[/TD]
[TD]Deb[/TD]
[TD]Cre[/TD]
[TD][/TD]
[TD]Acc[/TD]
[TD]Deb[/TD]
[TD]Cre[/TD]
[TD]Inc[/TD]
[TD]Dec[/TD]
[TD]Deb[/TD]
[TD]Cre[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try:
Code: 
Sub CompareCols()
    Application.ScreenUpdating = False
    Dim Val As String, desWS As Worksheet, i As Long, v1 As Variant, v2 As Variant
    Set desWS = Sheets("result")
    v1 = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    v2 = Range("I2", Range("I" & Rows.Count).End(xlUp)).Resize(, 7).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v1, 1)
            Val = v1(i, 1) & "|" & v1(i, 2) & "|" & v1(i, 3)
            If Not .Exists(Val) Then
                .Add Val, Nothing
            End If
        Next i
        For i = 1 To UBound(v2, 1)
            Val = v2(i, 1) & "|" & v2(i, 6) & "|" & v2(i, 7)
            If Not .Exists(Val) Then
                desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 3) = Array(v1(i, 1), v1(i, 2), v1(i, 3))
                desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1, 0).Resize(, 2) = Array(v2(i, 6), v2(i, 7))
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Macro to change way of showing data by transporting for each account name
2
Replies
19
Views
392
Alex Blakenburg
A
A
  • Solved
Create accounts by user form to show balance by calculation for two columns
Replies
4
Views
106
abdo meghari
A
A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
192
Alaa mg
A
O
  • Solved
transfer data from userform by matching items in combobox with headers in sheet
Replies
4
Views
216
Omar M
O
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
185
Omran Y
O

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top