+ Reply to Thread
Results 1 to 7 of 7

Compare column A between 2 worksheets and sum respective columns

  1. #1
    Registered User
    Join Date
    10-01-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Compare column A between 2 worksheets and sum respective columns

    Hi

    I have 2 worksheets with few common data in Column A. From column B to J has numeric values in it. What I need is, if column A in worksheet 1 matches column A in worksheet 2, then add the respective values in column B to J and show that in worksheet 3. If not then simply paste all columns.

    For Ex: worksheet 1 looks like

    020200 208 52 143 142 172 105 27 39 224
    020210 84 50 83 63 50 97 62 78 63
    020220 22 23 29 26 22 5 20 25 49
    020280 62 15 51 29 36 24 32 30 22
    020296 3 0 2 0 0 2 0 0 0
    020300 62 58 81 55 80 78 72 70 53

    worksheet 2 looks like

    020200 1 2 23 0 1 0 1 3 24
    020210 1 2 2 1 2 6 5 5 17
    020220 4 9 6 6 6 0 1 5 4
    020280 12 2 8 17 13 13 0 4 5
    020300 16 18 3 3 1 4 15 0 0
    020310 26 25 31 37 41 42 32 44 44

    Worksheet 3 should look like this

    020200 209 54 166 142 and so on.

    means sum of the column values.

    Can somebody help me?

    Thank you
    Attached Files Attached Files
    Last edited by brahmathereturn; 10-01-2010 at 02:52 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Compare column A between 2 worksheets and sum respective columns

    Hi brathmathurn,

    If the location of the data on all 3 sheets is the same..... BIG IF....

    You would sum the cells on sheet3 cell B2 with a formula like "=Sheet1!B2+Sheet2!B2"

    If your 020200 type rows are not in the same location on all 3 sheets the above doesn't work.

    If this doesn't get you any closer to an answer you should attach your sample sheet so we can get a better method for you.
    Last edited by MarvinP; 10-01-2010 at 03:00 PM.

  3. #3
    Registered User
    Join Date
    10-01-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Compare column A between 2 worksheets and sum respective columns

    Thanks for your input. I added a sample sheet. In both worksheets I have about 14000 rows with all columns filled in it. The rows are not in the same location in both worksheets. Please have a look at both the worksheets. Column A in worksheet 1 may or may not have the same value as Column A in worksheet 2. on worksheet 3 I'd like to have a sum of these numbers if they match, if not simply paste them from both the work sheets.

    Thank you
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-01-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Compare column A between 2 worksheets and sum respective columns

    Anybody? Please help

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Compare column A between 2 worksheets and sum respective columns

    Hi brahmathereturn,

    Find the attached that mostly solves your problem.
    I took column A in both sheets and copied them to Sheet3 (renamed to totals). Then I cheated and deleted duplicates, a feature in Excel 2007. You may not have this feature in 2003.

    I then selected all the data on sheet1 and named it Table1 (a named range). I did the same for sheet2.

    Then look at the formula for each cell to get the total. I'm hoping 2003 has the =IfError function. If not you need to do it a different way. The formula in B2 that copies across and down the entire Totals Sheet is:

    Please Login or Register  to view this content.
    Is it time to upgrade to Excel 2007 that has more/better functions?

    Does anyone want to convert the formula above to 2003 excel?
    Attached Files Attached Files

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Compare column A between 2 worksheets and sum respective columns

    Try this

    1/. Copy all the data, including the headers, from Sheet 100 to Sheet3
    2/. Copy the data, excluding the headers, from Sheet 200 to Sheet3 to first free row

    With Sheet3
    3/. Sort on column A
    4/. Copy the headers to M1
    5/. In M2
    Please Login or Register  to view this content.
    Drag/Fill down to the end of the data
    6/. In N2
    Please Login or Register  to view this content.
    Drag/Fill across to the last header column
    Then Drag/Fill down to the last used row.
    7/. Copy this all of new table and Paste Special > values to A1
    8/. Delete the formula table
    9/. Filter Column A for blanks
    10/. Select all of the resultant rows and Delete > Delete Sheet Rows
    11/. Remove the filter and it's done.

    This takes less time to do than explain, or indeed, read!

    I have set up a table in the attached workbook (up to step 7) to let you see the method.
    You can take it from there.

    Hope this helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  7. #7
    Registered User
    Join Date
    10-01-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Compare column A between 2 worksheets and sum respective columns

    Dear Marcol and Marvin

    Thank you so much for your inputs. That was great input. Marcol's method worked like a charm.

    Thanks again and sorry for taking so much time to get back to you guys. We were going through a major transition

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1