compare related data stored in two or more excel sheets
How to compare related data stored in two or more different excel sheets?
For example, annual sales figure of a number of companies relating to two OR more years available in different excel sheets?
Either by grouping (pooling) the data into a single excel sheet or otherwise?
REVENUE ACCOUNT for the year ended 30th June 2009
INCOME 2009 2008
Management fees 0.00 62,000.00
Interest received 0.00 5,854.95
Dividend received 0.00 291.20
Capital gain on
disposal of investmts 0.00 4,259.05
Total income 0.00 72,405.20
LESS EXPENDITURE
Accountancy fees 0.00 1,556.50
Advertising/promotion 0.00 1,777.24
Bank charges 0.00 15,213.10
Depreciation 0.00 3,375.00
Filing fees 0.00 359.00
Interest paid 0.00 164,584.26
Internet services 0.00 332.27
Motor vehicle expenses 0.00 4,956.22
Repairs and maintenance 0.00 241.30
Stationery 0.00 9,711.88
Superannuation 0.00 51,000.00
Telephone 0.00 6,070.26
Travel 0.00 3,009.94
Salaries and wages 0.00 115,372.00
Total expenses 0.00 377,558.97
NET PROFIT/(LOSS) 0.00 -305,153.77
How to compare related data stored in two or more different excel sheets?
For example, annual sales figure of a number of companies relating to two OR more years available in different excel sheets?
Either by grouping (pooling) the data into a single excel sheet or otherwise?
This will require common information in both/all sheets. Company name, ID, reference code...something that is identical and specific to each company and consistent across all the sheets.
With that code in hand, you can create a summary sheet to collate information from all the sheets into one. This can be done via a macro that you run "on-demand", or it can happen in realtime with formulas that pull the information together as the various other source sheets are updated.
If you'd like assistance on this, post up usable sample sets of sheets with enough data in them for me to show you how to do this. Make sure all the hurdles that need to be overcome are represented in your sample sheets...meaning don't make it so simplified it's problematic to implement.
Click on GO ADVANCED and use the paperclip icon to attach your workbook(s).
If you want to create a comparison like the one illustrated above, just use cell references.
For example lets say you have a layout as illustrated and you have 2008 data in one sheet and 2009 data in another sheet. You create a master sheet and copy the row labels. Lets say Management Fees is in row 4, col B of Sheet1. Then in the 2008 column
you would enter:
=Sheet1:B4
You can then copy this formula down the column. You can also do it by pointing. In the master sheet press the = key, then navigate to the cell you want to reference and press enter.
vks64 agrees: good. can you help me further as how to bring the related information in two sheets into a single sheet
Quote:
Originally Posted by JB
If you'd like assistance on this, post up usable sample sets of sheets with enough data in them for me to show you how to do this. Make sure all the hurdles that need to be overcome are represented in your sample sheets...meaning don't make it so simplified it's problematic to implement.
Click on GO ADVANCED and use the paperclip icon to attach your workbook(s).
Still looking for those samples to help you with basic layout. The layout and methods to use are greatly affected (often) by the data itself. So let's see the good sample data.
Originally Posted by JB
If you'd like assistance on this, post up usable sample sets of sheets with enough data in them for me to show you how to do this. Make sure all the hurdles that need to be overcome are represented in your sample sheets...meaning don't make it so simplified it's problematic to implement.
Sample excel worksheets are attached. For the tables given in excel sheets 1 and 2, would like to obtain Results similar to the ones in excel sheets 3 or 4 where the objective is to merge the contents of given tables in sheets 1 and 2 either by selecting all the related colomns (if possible) or atleast one or more of the related columns from each of the table in (excel sheets 1 and 2).
Suppose if excel worksheets does not have any solution where can a solution be found, perhaps in MS Access? Please help?
Frankly, I don't see the value of automating this unless you will be replacing the dates in sheets 1 and 2. This data looks like its pretty static, meaning that once you enter it into sheet 1 and 2 you will not change the data. So all you really need to do is copy and paste from sheet 1 and 2 into the appropriate coluimns in sheets 3 & 4.
However, if you want to automate it, do as I suggested in my response. For example, to reference the April 16 close for DLF in Sheet 3 enter the formula:
=Sheet1!C3
If you copy that formula down the column, it will pull all the April 16 closes. You can repeat the process for each column of data.
Scott's right. If Sheet1 comes in exactly as shown every day, it's just as fast to just manually copy it to Sheet 3 as anything else.
Now, if Sheet1 is "expanding"...growing, maybe automation is worthwhile.
Did you meet my requirement regarding making sure the uploaded sample completely represents all the hurdles that have to be overcome? That sample isn't dumbed down too far, is it?
Linear Mode
