Compare column A between 2 worksheets and sum respective columns

1. 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

2. 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.

3. 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

5. 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:

Is it time to upgrade to Excel 2007 that has more/better functions?

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

6. 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
Drag/Fill down to the end of the data
6/. In N2
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.

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

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

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