Need to Combine 2 Workbooks with a Common Column Header

**Mike_R1** · 10-14-2009, 12:43 PM

I have 2 Excel workbooks that were sent to me and I need to combine them. They have a common column called "Product SKU," but each additional and different columns. How can I combine the 2 workbooks so that one master workbook shows all of the common column data for each Product Sku.

Also, and I don't know if this matters, one of the workbooks has some additional Product Skus that aren't on the other workbook.

Thank you for any advice.

**JBeaucaire** · 10-14-2009, 01:50 PM

Post up a sample of each of the workbooks. Click on GO ADVANCED and use the paperclip icon to upload them.

Do you want the "merge" to be in one of the two workbooks, or into a third?

**Mike_R1** · 10-14-2009, 02:33 PM

Okay, I have attached samples of the two workbooks with the first 40 lines. To make things easier, we can just combine "AGN-Pricing.xls" into "AGN-Pricing-main.xls."

Please take a look at this and let me know the proper procedure for combining these two workbooks.

Thanks,

Mike

**JBeaucaire** · 10-14-2009, 04:21 PM

1) In the MAIN sheet, put the titles RETAIL/COST/QTY in C1/D1/E1

2) In C2, enter this formula then copy it down the whole dataset and across:

=IF(ISNUMBER(MATCH($A2,[AGN_Pricing.xls]CustPricing!$A:$A,0)), INDEX([AGN_Pricing.xls]CustPricing!B:B,MATCH($A2,[AGN_Pricing.xls]CustPricing!$A:$A,0)), "")

Once all the values appear:
3) Highlight the 3 new columns
4) Ctrl-C to copy
5) Click on Edit > Paste Special > Values

The sheets are now merged and the formulas have been removed.

**Mike_R1** · 10-15-2009, 01:38 AM

That did the trick. Thank you very much. Do you know a place where I can view a tutorial on writing formulas like that? I always like learning these things and expanding my Excel skills.

**JBeaucaire** · 10-15-2009, 11:12 AM

This forum is a wonderful tutorial. Pick a topic/function/problem and do a search in the database of the forum. Read through the threads where people have asked about that topic and the answers given. Analyze the answers until you understand them.