Previous Topic Next Topic

Compare 2 Sheets -> Summary differences

Solved/Closed
SidK Posts 6 Registration date Wednesday April 29, 2015 Status Member Last seen October 13, 2015 - Apr 29, 2015 at 10:41 AM
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 :)
Related:

3 responses

Answer 1 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 30, 2015 at 04:00 AM
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
SidK Posts 6 Registration date Wednesday April 29, 2015 Status Member Last seen October 13, 2015
Apr 30, 2015 at 04:20 AM
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!
Answer 2 / 3
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Apr 30, 2015 at 04:27 AM
you are welcome
Answer 3 / 3
SidK Posts 6 Registration date Wednesday April 29, 2015 Status Member Last seen October 13, 2015
Apr 30, 2015 at 07:26 AM
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?