Compare Data from Two different tabs and to identify variance items

**Excel Dumbo** · 10-11-2012, 06:34 PM

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 work the magic.

Any help would be greatly appreciated.

Excel Dumbo

**benishiryo** · 10-11-2012, 09:47 PM

hi there, you could have a table of VLOOKUP to compare or combine both data, add in a column for Qtr & do a pivot. i did the former since pivot should be easier for you. with this table, you can also add Data Validation for users to choose between the Qtr

**Excel Dumbo** · 10-16-2012, 05:32 AM

Hi Benishriyo,

Thanks for your reply to my query

I would like to understand, as how you copied the values in "A" column in "comparisson" work sheet

Thanks a lot for your time and effort

**benishiryo** · 10-16-2012, 09:41 AM

you're very welcome. i actually did something a little troublesome. i copied your formula without the "=" sign. so it's:
I27+I30+I33+I36+I38+I42+I44+I47+I50+I52+I61+I110+I126+I128+I130+I194+I228+I236+I264+I268+I271+I293+I295

paste it somewhere in say R3. go to Data -> Text to Column -> Delimited -> Next -> Others: + -> Finish
so now, it'll separate by the "+" sign & you'll have all the column I reference.

Select R3:AN3 & press CTRL + H to Find & Replace. Find what: I
Replace with: =B

it'll now refer to the text in Column B. you can then copy this whole list, go to the Comparison worksheet & Paste Special -> Values -> Transpose -> OK

there might be an easier method though