PDA

View Full Version : Solved: calculate difference between 2 excel files



justdream
02-17-2011, 05:59 AM
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
02-17-2011, 06:21 AM
If you can post a small sample of typical data on two sheets and show the result you are after, it would assist.

justdream
02-17-2011, 06:46 AM
here it is..
Thanks in advance

mdmackillop
02-17-2011, 12:05 PM
How should the resulting sheet look?

justdream
02-17-2011, 12:29 PM
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
02-17-2011, 02:21 PM
With both books open, run the following macro in the Today book


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

justdream
02-18-2011, 05:47 AM
Amaaaaaaaaazing :clap:

justdream
02-19-2011, 10:01 AM
Dears,

kindly advice how to delete above attachments..

Jacob Hilderbrand
02-19-2011, 10:15 AM
I have deleted the attachments.

Thanks

mdmackillop
02-19-2011, 10:29 AM
Hi JustDream,
Can you please reposrt a sanitised workbook to make the solution meaningful to others.
Regards
MD

justdream
02-19-2011, 11:43 AM
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


Set Rng = Rng.Offset(, 4).Resize(, 3)