VBA Code For comparing Rows

[lalsigongon]

Hi

I am trying to create a VBA code that allows me to compare rows data between last month and this.
For example.
Last month
1 A
2 B
3 C
4 D
41 E
5 F
6 G
7 H


This month
1
2
2a
3
4
5
5b
6
7
8

Thus, I would manual check row by row, to ensure that the data in lastmonth is update to date. this means, I have to add in 2a and 5b as additional rows into the data file and also delete 41 before I could use the file.
is there anyway to automate this via macro?

I found a way to add rows but not delete unnecessary ones.

"Dim cl As Range

Set cl = Range("C1")

Do While cl.Row < 10
If cl.Value <> cl.Offset(0, 3).Value Then
cl.Offset(0, 1).Insert Shift:=xlDown
cl.Insert Shift:=xlDown
Set cl = cl.Offset(-1, 0)
End If
Set cl = cl.Offset(1, 0)
Loop

End Sub"

Might not be the best way! hope someone can help me out!

 

[kalak]

Could you be more specific about just what it is you want compared other than just "rows", also where are your data are located on the worksheet, why do you have to add in 2a and 5b and delete 41, and what should your desired result look like?

 

[lalsigongon]

Hi !

sorry about that. Let me explain more.

I work in accounts and every month SAP's Conso program, churns out a list of Account codes with their balances, I then have to compile this into a report.

The list that SAP churns out always have a different range of account codes

sometimes its 1,3,5,6,7,8,9,10
Then the next month is 1,2,3,4,5,6,7,9,10.

I need to reconcile the accounts on the previous list and the new list. In this case, acct 2 and 4 are missing and account 8 is no longer needed.

The issue here is also that the conso program is giving me a YTD number and I need the monthly figures, thus, after I reconcile the list, I take the month's ytd balance to minus the previous month balance.


I have attached a before and after photo of what I need!

Before

After

 

[kalak]

Here's a VBA code that seems to do something like you ask.
It starts from the same inputs that you posted and produces something like the output you wanted.

But there are differences in the code's output and the output you posted.
You can compare these differences, although first recheck that your posted output does indeed correctly follow from your posted input, and then consider ...
The code assumes the starting data are in the first four columns of the active worksheet.

Sub maybe_compare_rows()

Dim ash As Object, a, b
Dim i As Long, ra As Long, rc As Long

Set ash = ActiveSheet
ra = Cells(Rows.Count, "a").End(xlUp).Row
rc = Cells(Rows.Count, "c").End(xlUp).Row
a = Range("A1:A" & ra)
b = Range("C1:C" & rc)

With Sheets.Add
    For i = 2 To ra
        ash.Cells(i, "a").Resize(, 2).Copy .Cells(a(i, 1), 1)
    Next i

    For i = 2 To rc
        ash.Cells(i, "c").Resize(, 2).Copy .Cells(b(i, 1), 3)
    Next i

    .UsedRange.Copy ash.Cells(2, 1)
    Application.DisplayAlerts = False
        .Delete
    Application.DisplayAlerts = True
End With

With Range("A2").CurrentRegion
    .Resize(, 1).Offset(, 5) = Evaluate(.Columns(4).Address & "-" & .Columns(2).Address)
    .Resize(, 1).Offset(, 5).NumberFormat = "$#.00"
End With
Range("F1") = "Month of July"

End Sub

 

[lalsigongon]

Thanks will try and let you know!