Solved: calculate difference between 2 excel files

[justdream]

Dears,

I need your help..
I’ve daily data consists of many rows and columns..
I’d like to have macro to calculate difference between Today Excel and Yesterday Excel based on column D
I can do vlookup to get equivalent data from Yesterday excel into Today excel based on column D, then calculate the difference
But doing that for large amount of data and dynamic as Excel template itself may be could change in the near future is forcing me to search for smart solution “dynamic macro”..
So kindly advice how to do that..

[mdmackillop]

If you can post a small sample of typical data on two sheets and show the result you are after, it would assist.

[justdream]

here it is..
Thanks in advance

[mdmackillop]

How should the resulting sheet look?

[justdream]

it will be highly appreciated if the result will be as shown in below attachement:

- all columns with colored header to be contains Delta values "Difference between Today data & yetserday Data"

[mdmackillop]

With both books open, run the following macro in the Today book

[vba]
Option Explicit

Sub CompareData()

Dim Ths As String
Dim LstBk As String
Dim LstSht As String
Dim Fmla As String
Dim txt
Dim Rng As Range


Sheets(1).Copy After:=Sheets(1)
Sheets(2).Name = "Results"

With Sheets(2)
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
Set Rng = Rng.Offset(, 4).Resize(, 36)
Rng.ClearContents

Ths = Sheets(1).Name
LstBk = InputBox("Previous book name", , "Worst Cell List_Final_15.xlsx")
LstSht = InputBox("Previous sheet name", , "Worst cell list_15")

Fmla = "='ThsBook'!RC-INDEX('[lstbook]lstsheet'!C,MATCH(RC4,'[lstbook]lstsheet'!C4,0))"
Fmla = Application.Substitute(Fmla, "ThsBook", Ths)
Fmla = Application.Substitute(Fmla, "lstbook", LstBk)
Fmla = Application.Substitute(Fmla, "lstsheet", LstSht)

Rng.FormulaR1C1 = Fmla

End Sub[/vba]

[justdream]

Amaaaaaaaaazing

[justdream]

Dears,

kindly advice how to delete above attachments..

[Jacob Hilderbrand]

I have deleted the attachments.

Thanks

[mdmackillop]

Hi JustDream,
Can you please reposrt a sanitised workbook to make the solution meaningful to others.
Regards
MD

[justdream]

Dear mdmackillop,
For sure, here's another workbook to be used by others as draft data
Dear VBAexpress users, before using mdmackillop's code using my new attached data
just change line no. 14 to have that simple modification

[VBA]
Set Rng = Rng.Offset(, 4).Resize(, 3)
[/VBA]