Consolidating 2 excel workbooks with Excel 2010

[Corbet]

I'm having trouble consolidating two workbooks that have different information. I need the information from Workbook B, column "MTAX". In order to find out where the information "MTAX" goes in Workbook A, Excel must look at the "Client ID" and "Code" and make sure they BOTH matchup before moving over the "MTAX" information into Workbook A. What would be the easiest way to do this? The reason why I need both column to match up is because it is possible for there to be multiple "CLIENT ID" matches with different "CODE".

For reference, the workbook columns look like this:

Workbook A:

Client ID | CODE | MTAX
001 | ABC |
001 | DEF |
002 | ABC |

Workbook B:
Client ID | CODE | MTAX
001 | ABC | $50
001 | DEF | $100
002 | ABC | $150

 

[xenou]

Hi, Welcome to the board.

You can find an explanation of how to do this at Debra Dalgleish's site under the heading Sum cells that match multiple criteria -- SUMPRODUCT

With Excel 2010 it's even easier now - you can use the new SUMIFS() function. Since you want a number value with two criteria (Code and Client ID) you can "Sum" the match since its a single value.

Be careful - both the SumProduct approach and SumIfs() will add up the values if there are more than one match.

Regards,
ξ

 

[Corbet]

I'm not sure I'm following. I'm not looking to calculate any sort of total. I wish to merge two workbooks with matching criteria but different information. Here's a better example:

Workbook A:
Client ID | CODE | MTAX
SuperM | CA SDI | _____
SuperM | CA SUTA | _____

Workbook B:
Client ID | CODE | MTAX
SuperM | CA SDI | $2949
SuperM | CA SUTA | $593

I need to get the MTAX information into Workbook A and have it match up with the correct Clients. I am unable to just copy the MTAX column over because Workbook A and Workbook B have different client IDs.

 

[xenou]

Correct. SumIfs() will sum the value based on the (two) matching criteria. In this example, I have the tables on the same sheet, but except for the location of the tables, the principle is the same:

<img alt="worksheet formulas" src="http://northernocean.net/etc/mrexcel/20110701_tables.png" />

Formula in Cell C2 (looks up the value from Table2):
=SUMIFS($C$6:$C$7,$A$6:$A$7,$A2,$B$6:$B$7,$B2)

 

[Corbet]

Correct. SumIfs() will sum the value based on the (two) matching criteria. In this example, I have the tables on the same sheet, but except for the location of the tables, the principle is the same:

<img alt="worksheet formulas" src="http://northernocean.net/etc/mrexcel/20110701_tables.png" />

Formula in Cell C2 (looks up the value from Table2):
=SUMIFS($C$6:$C$7,$A$6:$A$7,$A2,$B$6:$B$7,$B2)

Ah okay, I understand the SUMIFS function, but I don't think that would work very well for my needs. Having to manually code in the SUMIFS into thousands of different cells would take longer than comparing the two worksheets and manually coding in the MTAX information.

Any other ideas?

 

[xenou]

Enter the formula in the first cell, then copy it to the rest - no need to manually input it more than once.

ξ