Learn IT

Comparing data from two Excel worksheets

Question: I need some help analyzing some data from two different spreadsheets and was wondering if you would guide me on what the best way to do it is. I have two spreadsheets and one contains costs from 2013 and one for 2014 and I need to match the two codes on each spreadsheet so we can do a comparison of the costs from 2013 to 2014.

Answer: It is possible, but in looking a your spreadsheets, you need to do a few things first. I basically brought all the information together into one sheet and created a pivot table. But because the sheet you need was a result of a pivot table which showed years group and departments grouped, I was not able to work with that to modify the columns, so I copied the Pivot table values to a different worksheet and could then add new columns for the appropriate year and copied it down the column, then I copied down the departments. Then I made sure the headings were the same in both 2013 and 2014 sheets in the correct placement. Then I copied one worksheet data to the bottom of the other. I named that worksheet “combined data”. Then I created a pivot table from that called PIVOTALL. I’m not sure exactly  how you want to compare the data, but I placed the Years in columns and the ID number in the row and you can place the various columns of codes you wish to compare in the table filter section. You can rearrange pivot tables in a number of ways, so just start playing!

Leave a Reply