Help to check balance account number

[sbv1986]

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:

	A	B	C	D	E	F	G	H
1	Acc number	Debit	Credit	Increase	Decrese	Debit	Credit
2	10101	1000	100
3	10102	2000	200
4	10103	3000	300
5	10104	3000	300
6

<tbody>
</tbody>

sheets(balance02) like this:

	A	V	C	D	E	F	G	H
1	Acc number	Debit	Credit	Increase	Decrese	Debit	Credit
2	10101					1000	101
3	10102					2000	200
4	10103					3000	300
5	10104					3001	300
6
7	...

<tbody>
</tbody>

sheets(result) like this:

	A	B	C	D	E	F	G	H
1	Acc number	Debit balance01	Creadit balance01	Debit balance 02	Credit balance02
2	10101	1000	100	1000	101
3	10104	3000	300	3001	300
4

<tbody>
</tbody>

 

[mumps]

Try:

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

 

[sbv1986]

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:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Acc

Deb

Cre

Inc

Dec

Deb

Cre

Acc

Deb

Cre

Inc

Dec

Deb

Cre

2

3

4

5

6

7

8

9

<tbody>
</tbody>

 

[mumps]

Try:

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