• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare data from two different sheets and find variance cause

E

Ecel Dumbo

Member
Hello,


This is to do a variance check on the two different tabs of data for two different periods.

The operating expense amount calculated in Quarter 4 and Quarter 3 tabs in cells S16 and S15 respectively. As you can see in the spreadsheet Operating Expense in Quarter 4 is more than Operating expense in Quarter 3, I need to find out which items from the table causes this variance. A formula which could help do the job.


Any help would be greatly appreciated.


File link


http://speedy.sh/W8U6m/Variance-Analysis.xls


Download at SpeedyShare


Excel Dumbo
 
Hi Dumbo,


Please check the attached sheet.. If its help you in any case.. then we will discuss about the solution. :)


https://dl.dropbox.com/u/78831150/Excel/Variance%20Analysis%20%28Ecel%20Dumbo%29.xls


Regards,

Deb
 
Hi Deb, THanks a ton for your valuable time and effort. This is exactly what I want


My first question is how did u manage to copy the source data without the sub data in to another sheet.


You have also not included formula in the consolidated area. so i am having trouble understanding how u did this. If you could tell me in brief, that would be great


once again thans a lot deb. u rock!
 
Hi Dumbo,


* Select All data in Source, and press (Alt + ;) or (Alt + E + G + S + Y - Goto>Special>Visible Cells Only) before press Ctrl + C.

It will select only Source Data, without Sub Data, or Hidden Cell..


* I have not used any Formula to compare.. I have used Data > Consolidate. as both Source has ~(approx) same Left Column


Copy Only Left Columns from Quaretr 4 and, go to Data > Data Tools > Consolidate.


Consolidate helps to combine Two or more, look a like table in One. by comparing different Method.. i.e SUM, COUNT, MAX, MIN...


Select References, from where you need to Consolidate Table.

i.e Sheet1!$A$1:$D$65536 & Sheet2!$E$1:$E$65536 or select the area by Browse.


Then its CONSOLIDATE 's headache, how you need the comparison..


Play with it, Its very Interesting tools and used rare.. :)
 
You must log in or register to reply here.
Back
Top