Compare 2 Sheets -> Summary differences [Solved/Closed]

Posts
6
Registration date
Wednesday April 29, 2015
Status
Member
Last seen
October 13, 2015
- - Latest reply: SidK
Posts
6
Registration date
Wednesday April 29, 2015
Status
Member
Last seen
October 13, 2015
- Apr 30, 2015 at 07:26 AM
For an ongoing school project values of test subjects are changing over time.
I would like to create a quick overview of these changes.

Examples underneath are simplified:


Sheet 1: (original results)



Sheet 2: (new results / changes are yellow)


I would like to create a simple automated overview of all changes on sheet 3:
Preferably something like this:


I have been struggling with this for weeks now and still haven't found a way to do this easy. Hopefully somebody can help pushing me in the right direction to solve this :)
See more 

3 replies

Best answer
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
787
1
Thank you
download the file sidk 150430 from this webpage

http://speedy.sh/HuhfA/sidk-150430.xlsm

enable macro


see the macro test . sheet 3 is after macro is run. if you want to retest
delete rows 2 and below (keep row 1 as it is) in sheet 3. and run the macro test again
the macro test is given below also

Sub test()
Dim j As Integer, k As Integer, value As String
Dim m As Integer, n As Integer
Dim change As String, product As String, old_value As String, new_value As String

With Worksheets("sheet1")
m = Range("a1").End(xlDown).Row - Range("a1").Row
m = m + 1
'MsgBox m
n = Range("a1").End(xlToRight).Column - Range("a1").Column
n = n + 1
'MsgBox n
End With
For j = 2 To m
For k = 2 To n
If Worksheets("sheet1").Cells(j, k) <> Worksheets("sheet2").Cells(j, k) Then
With Worksheets("sheet1")
change = .Cells(1, k)
product = .Cells(j, 1)
old_value = .Cells(j, k)
End With
new_value = Worksheets("sheet2").Cells(j, k)
With Worksheets("sheet3")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = change
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = product
.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = old_value
.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = new_value
End With
End If
Next k
Next j
End Sub

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 5781 users have said thank you to us this month

SidK
Posts
6
Registration date
Wednesday April 29, 2015
Status
Member
Last seen
October 13, 2015
-
Hi Venkat,

Thank you very much for your help, so much appreciated!

I have just tried your macro in the workbook you created and it actually seems to work perfectly. (even with adding extra columns and rows)

I will be back in school soon; where I can try it with the full datasheets (containing at about 200 columns and 2000 rows). As soon as I have tested it I will let you know.

Thanks again!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
787
0
Thank you
you are welcome
Posts
6
Registration date
Wednesday April 29, 2015
Status
Member
Last seen
October 13, 2015
0
Thank you
Just tried it together with my group members; who are superexcited and giving you a big thank you as well! Very much appreciated.

The macro works, except for one last challenging "detail" being caused by added or deleted test subjects.

This has not been mentioned before, so maybe I should start another topic for it, but it changes the situation like this:

Sheet 1: (Original results)


Sheet 2: (new results / changes are yellow)


Results on sheet 3:


The proposed results may look like this, but I am open to other similar suggestions that can be easier to realize. Do you think something like this may be possible?