Ask Experts Questions for FREE Help !
Ask
    vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #1

    Apr 14, 2009, 07:35 PM
    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?
    ROLCAM's Avatar
    ROLCAM Posts: 1,420, Reputation: 23
    Ultra Member
     
    #2

    Apr 14, 2009, 08:50 PM

    Bring them close to each other.

    Example:-

    REVENUE ACCOUNT for the year ended 30th June 2009

    2009 2008

    INCOME

    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 investments 0.00 4,259.05

    Total income 0.00 72,405.20


    LESS EXPENDITURE

    Accountancy fees 0.00 1,556.50
    Advertising and promotion 0.00 1,777.24
    Bank charges 0.00 15,213.10
    Depreciation - motor vehicles 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 OPERATING PROFIT/(LOSS) 0.00 -305,153.77
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #3

    Apr 14, 2009, 11:58 PM
    Quote Originally Posted by ROLCAM View Post
    Bring them close to each other.
    Code:
    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
    Use code tags to make your charts legible.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Apr 15, 2009, 12:05 AM
    Quote Originally Posted by vks64 View Post
    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).
    ROLCAM's Avatar
    ROLCAM Posts: 1,420, Reputation: 23
    Ultra Member
     
    #5

    Apr 19, 2009, 03:13 AM

    There is definitely a way to do this through
    EXCEL.
    Press WINDOW.
    Go to New Window.
    Choose the two windows you want to compare.
    Compare side by side.

    My suggestion:-

    Pick a specific example .
    Experiment with the above until you get it to work.

    Good luck.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Apr 19, 2009, 04:57 AM

    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #7

    Apr 19, 2009, 04:45 PM

    Quote Originally Posted by vks64
    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.
    vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #8

    Apr 20, 2009, 10:01 AM
    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 at least 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?
    Attached Files
  1. File Type: xls askmehelp.xls (17.0 KB, 275 views)
  2. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    Apr 20, 2009, 11:33 AM

    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #10

    Apr 20, 2009, 01:40 PM

    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?
    vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #11

    Apr 22, 2009, 08:05 AM
    Quote Originally Posted by JBeaucaire View Post
    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?
    Sir, You have correctly assessed my view and requirement. The sample enclosed is a selected list of shares traded in the National Stock Exchange of India. Actually the total number of rows in the above table on any day is approximately in the range of 1200 to 1400. Either Sheet 1 or 2 is growing(or diminishing) every day(depending upon the number of shares actually traded on that particular day) and keeps on changing. Therefore the number of rows are never equal on any two days when taken for comparison. Further all the rows appearing on a day need not repeat on the following day resulting in omission or commission of rows. Because of the difference in the number of rows in tables 1 and to the two or more tables taken for comparison could not be copied and pasted manually into a single table (as suggested) and requires automation for the purpose of feeding/matching all the values exactly against the name of the stock(in a single table).
    I want the sample tables to appear as consolidated and presented by you in your example but with more than two columns taken up for comparison
    vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #12

    Apr 22, 2009, 08:25 AM
    Quote Originally Posted by ScottGem View Post
    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.
    Sir,
    Your suggestions were of great use and I would certainly make use of it in simple and smaller applications everyday for which I thank you wholehearted. But I would like to say you that my daily needs are something more complex requiring comparison and updating of the existing data in the excel sheets as I have narrated in my rejoinder as above. I would appreciate if you can guide me in this sphere with simple applications. Your suggestions as above "=Sheet1!C3" were tried my me and was found working. But the earlier suggestion as "=Sheet1:B4" given in your first reply did not work and it gave a result like this "#NAME?" in the answer column. Lastly, I would request you whether the above problem has a easier solution in MS Access as I know a bit of MS Access to work? If so can you guide in that area also?
    Thanking in Advance once again!
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #13

    Apr 22, 2009, 11:29 AM

    Okay. Macro it is then. I still fear things in your example don't reflect fully the situation you will be in when you try to run the macro.

    I think your Sheet3 example is easiest to read.

    1) Is the sheet to be evaluated in the same workbook and it is called Sheet1?
    2) The sheet to put the answers on is called Sheet3?
    3) The sheet to put the answers on retains the info already there and gets two new column each time it is run adding the date in row1, the headers in row 2, and the data below that?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #14

    Apr 22, 2009, 12:09 PM
    Give this macro a try on your original sheet. Just change the dates on your sheet3 to dates earlier than April 16, then run the macro, it will add all the data from sheet1.

    Next time you run it, I am presuming the date is a later date, so it will let you run it again. Try to run it twice on the same data...
    Code:
    Option Explicit
    
    Sub TrackTopTenTrades()
    Dim nextcol, lastrow, i As Long
    Dim wsInp, wsOut As Worksheet
    Dim test As Variant
    
    Set wsOut = Sheets("Sheet3")        'Set your output report sheet
    Set wsInp = Sheets("Sheet1")        'Set your input data sheet
    nextcol = wsOut.Cells(2, Columns.Count).End(xlToLeft).Column + 1
    lastrow = wsInp.Cells(Rows.Count, 1).End(xlUp).Row
    
    'verify this hasn't been done already
    If wsOut.Cells(1, nextcol - 2).Value > wsInp.Cells(3, "B").Value Then
        MsgBox "Data for this date was already collected, aborting..."
        Exit Sub
    Else
        MsgBox "Data for this date not found in archive, adding data..."
    End If
    
    'Add data to report
    'Titles
        wsOut.Range(Cells(1, nextcol), Cells(1, nextcol + 1)).Merge
        wsOut.Range(Cells(1, nextcol), Cells(1, nextcol + 1)).HorizontalAlignment = xlCenter
        wsOut.Cells(1, nextcol).Value = wsInp.Cells(3, "B").Value
    'Data
        wsOut.Cells(2, nextcol) = "CLOSE"
        wsOut.Range(Cells(3, nextcol), Cells(11, nextcol)).FormulaR1C1 = "=INDEX(Sheet1!C3,MATCH(RC1,Sheet1!C1,0))"
        wsOut.Cells(2, nextcol + 1) = "TOTTRDVAL"
        wsOut.Range(Cells(3, nextcol + 1), Cells(11, nextcol + 1)).FormulaR1C1 = "=INDEX(Sheet1!C6,MATCH(RC1,Sheet1!C1,0))"
        wsOut.Range(Cells(1, nextcol), Cells(1, nextcol + 1)).Columns.AutoFit
    'Remove formulas and convert to flat data
        wsOut.Range(Cells(1, nextcol), Cells(1, nextcol + 1)).Copy
        wsOut.Range(Cells(1, nextcol), Cells(1, nextcol + 1)).PasteSpecial xlPasteValues
        wsOut.Columns(nextcol + 1).AutoFit
        Application.CutCopyMode = False
    
    End Sub
    shankar79's Avatar
    shankar79 Posts: 1, Reputation: 1
    New Member
     
    #15

    Apr 24, 2009, 07:29 PM
    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.

    Sorry to interrupt! I too have a few doubts in excel. I have enclosed some amount of related information stored in two sheets and I want to make an union of the two sheets similar to the way you did in your reply posted on Apr 15, 2009, 06:58 AM. Also explain me the way you did for the union of sheets in your post on Apr 15, 2009, 06:58 AM
    Further can I separate the information based on the gender from the union of sheets
    Attached Files
  3. File Type: xls Book1.xls (15.0 KB, 186 views)
  4. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #16

    Apr 24, 2009, 10:46 PM

    Shankar, next time just start a new thread of your own.
    Code:
    Option Explicit
    
    Sub Consolidate()
    'Merge data on Sheet A and Sheet B onto SUMMARY
    Dim i, lastrow As Long
    
    Sheets("Summary").Activate
    'Clear existing data
        Cells.ClearContents
        Cells(1, 1) = "Student"
        Cells(1, 2) = "Gender"
    
    'Copy data to Summary
    lastrow = Sheets("Sheet A").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet A").Range("A3:C" & lastrow).Copy
    lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lastrow).PasteSpecial xlPasteValues
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Columns(3).Insert Shift:=xlShiftToRight
    
    lastrow = Sheets("Sheet B").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet B").Range("A3:C" & lastrow).Copy
    lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lastrow).PasteSpecial xlPasteValues
    
    'Sort names
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
        Range("A3:D" & lastrow).Sort Key1:=Cells(3, "A"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
    
    'Remove duplicates while merging data
        For i = lastrow To 3 Step -1
            If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
                Cells(i - 1, "C").Value = Cells(i, "C").Value
                Rows(i).Delete Shift:=xlShiftUp
            End If
        Next i
    
    'Add event titles
    Cells(1, 4) = Sheets("Sheet A").Cells(2, "C")
    Cells(1, 3) = Sheets("Sheet B").Cells(2, "C")
    Columns("C:D").Columns.AutoFit
    Columns("C:D").HorizontalAlignment = xlCenter
    Range("A1").Select
    
    End Sub
    On the sample SUMMARY sheet there is a Forms Button called "Consolidate"... click that button and the data from "Sheet A" and "Sheet B" will be consolidated onto the one summary sheet.
    Attached Files
  5. File Type: xls Consolidate.xls (34.0 KB, 226 views)
  6. vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #17

    May 19, 2011, 11:57 AM
    Quote Originally Posted by JBeaucaire View Post
    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?
    Dear sir,
    First accept my apology for not visiting the site for a long period and hence could not attend your remarks.

    Now I enclose the sample excel sheet to illustrate my need.

    Sheets no 1 to 4 of sample Worksheet 1 represent the End of Day statistics of the shares traded in the national stock exchange of india(NSEI), for the dates 16th ,17th and 18th of may 2011.

    Sheet no. 4 of sample worksheet 2 represents the portfolio of shares maintained and the information contained in the portfolio was that extracted from the EOD statistics of 16th of may 2011.

    Now the actual demand is as follows:

    1) The Portfolio has to be updated everyday on a daily basis from the EOD data released by the exchange. i.e.. the data in the portfolio will keep changing everyday at the end of the day.

    2) The data for the portfolio has to be extracted from the EOD statistics for the day. Since
    the EOD statistics contains large number of items, manual extraction is very much difficult, if not possible. With expansion in the size of the portfolio as well as the EOD data, automatic updating (extraction) of data is very much essential.

    3) Further, to compare the performance of items in the portfolio, values like price, volume(traded Quantity) etc of individual shares forming part of the portfolio has to be collated in a single sheet and studied as in workbook 2.( price factor alone compared in sample workbook 2)
    I use MS Office 2007 and I have only basic knowledge in the operating of computers. Hence please offer your remarks at the simplest language possible.

    Thank You,
    Vks64
    Attached Files
  7. File Type: xls sample workbook 1.xls (200.0 KB, 127 views)
  8. File Type: xls Sample workbook 2.xls (20.5 KB, 118 views)
  9. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #18

    May 19, 2011, 03:13 PM

    Personally, I would just do this all in one sheet without a macro, a simple LOOKUP() formula is all that is needed to lookup the portfolio items on each sheet and add them to a new column on your growing Portfolio-Study sheet.

    Anyway, move the Portfolio-Study sheet into the same workbook as the raw data. Add new sheets with new raw data anytime you want, then run this macro. It will add the new data to the study sheet. After that, you can delete the raw data sheets if you want, or move them elsewhere.

    Code:
    Option Explicit
    
    Sub CreateStudySheet()
    Dim ws As Worksheet
    Dim NC As Long, Check As Long, LR As Long
    
    With Sheets("Portfolio-Study")
        NC = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        
      For Each ws In Worksheets
        If InStr(ws.Name, "folio") = 0 Then
            On Error Resume Next
            Check = Application.WorksheetFunction.Match(ws.Name, .Range("1:1"), 0)
            If Check = 0 Then
                .Cells(1, NC) = ws.Name
                With .Range(.Cells(2, NC), .Cells(LR, NC))
                    .FormulaR1C1 = "=LOOKUP(RC1, '" & ws.Name & "'!C1, '" & ws.Name & "'!C5)"
                    .Value = .Value
                End With
                NC = NC + 1
            Else
                Check = 0
            End If
        End If
      Next ws
    End With
        
    End Sub
    Attached Files
  10. File Type: xls Sample workbook 1.xls (233.5 KB, 122 views)
  11. vks64's Avatar
    vks64 Posts: 59, Reputation: 1
    Junior Member
     
    #19

    May 22, 2011, 09:53 AM
    Comment on JBeaucaire's post

    Thank You

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Search and compare in excel [ 3 Answers ]

In an Excel spreadsheet, I want to search for multiple matches based upon the entry in a cell. That is... Col A Col B Col C x x 1 y y 2 z 3 x 4 Using the...

Compare 2 Tables or 2 Spread sheets [ 9 Answers ]

Excel 2007: I want to be able to compare TWO (2) Columns or two spread sheet. For Example I have a Column of students and corresponding records; Then I have another column similar to the first one but essentially more/less students. I want to be able to compare both columns and get rows...

Data cleansing in excel [ 1 Answers ]

Every day I get reports of more than thousand rows for data cleansing. In that report mainly we have to delete all the related records as per amount like +100 and -100 should be deleted. I have attached the sample report here for your reference.

How to compare Excel Lists with a free tool? [ 1 Answers ]

I need to compare Excel lists (1-3 columns) and isolate non-common list entries in a separate new list. Do you know of any tool that automatically performs this task and is free to use? One can usually search for such utilities by googling "Excel diff". Thank you in advance.


View more questions Search