Changelog - to see what rows been added or removed between 2 sheets?

[sbi85]

Hey all,

This is something I've been trying to solve but never managed.

We have a report about (let's say) product usage that was pulled at Week 1 - stored in Worksheet1. Then we pull a report on this again a week later - stored in Worksheet2. There are new products entering the second week and there are ones that are not used anymore so they went away (plus there are some with no change).

To visualise it this way (it's better to view this in the attached example file though):

Now what I'd like to have on Worksheet3 is the list of changes from Week1 to Week2. To list the rows that were either added or removed for Week 2.
So something like this:

(Entity = think of it as companies)

Example file: Changelog - example dataset

How to track the changes from Worksheet1(Week1) to Worksheet2(Week2) as sort of a changelog in a third sheet?

Please let me know how you would solve this or any advice you can give!
Any help is greatly appreciated.

Balint

--------------
As for a bit more detailed context (perhaps it's helpful) the data in Worksheet2 would be put into Worksheet1 every week and the new week's data put into Worksheet2 to always compare the last 2 weeks of data.

And in case this is helpful for someone, (but I felt it's more difficult to understand what the data is about this way), the IDs are actually Salesforce Opportunities and I am trying to track the week-on-week changes of potential sales to see what potential deals are being added to the pipeline and what is getting removed from the pipeline. And I am pulling reports into excel on a weekly basis to analyse the current status of pipeline.

 

[mrshl9898]

Depending how the book is layed out:

Sub Compare()

Dim C As Worksheet
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim rownum As Long
Dim Crownum As Long


Set ws1 = Sheets("Week1") ''rename as needed
Set ws2 = Sheets("Week2") ''rename as needed
Set C = Sheets("Compare") ''rename as needed

    ws2.Range("D2").FormulaR1C1 = "=RC[-3]&RC[-2]"
    ws2.Range("E2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Week1!C[-1],1,0),""added"")"
    ws2.Range("D2:E2").Copy ws2.Range("D2:E" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)

    ws1.Range("D2").FormulaR1C1 = "=RC[-3]&RC[-2]"
    ws1.Range("E2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Week2!C[-1],1,0),""removed"")"
    ws1.Range("D2:E2").Copy ws1.Range("D2:E" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row)
   
rownum = 2
Crownum = 2
    Do Until ws1.Cells(rownum, 1) = ""
        If ws1.Cells(rownum, 5) = "removed" Then
            ws1.Rows(rownum).Copy C.Rows(Crownum)
            Crownum = Crownum + 1
        End If
    rownum = rownum + 1
    Loop
   
rownum = 2
    Do Until ws2.Cells(rownum, 1) = ""
        If ws2.Cells(rownum, 5) = "added" Then
            ws2.Rows(rownum).Copy C.Rows(Crownum)
            Crownum = Crownum + 1
        End If
    rownum = rownum + 1
    Loop
   
ws1.Columns("D:E").ClearContents
ws2.Columns("D:E").ClearContents
C.Columns("D").Delete
   
End Sub