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

sbi85

New Member
Joined
Aug 19, 2015
Messages
6
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):

Screenshot_83.png


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:

Screenshot_1.png


(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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Depending how the book is layed out:

VBA Code:
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

1620870186762.png

1620870203176.png
 

Forum statistics

Threads
1,141,075
Messages
5,704,156
Members
421,330
Latest member
imdumb

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
Top