Merge 2 sheets with common ID column

**imimin** · 04-19-2011, 01:13 PM

Hello!

I need a MACRO that will compare 2 (or more?) sheets from the same work book. Each sheet has a common ID (column A). Other than that, all columns are different with different data. What I need to to do is is combine (merge) all columns from different sheets with the same ID. The problem that one both of the following could be true:

1) The IDs may NOT be in the SAME ORDER from sheet to sheet.
2) Some IDs that are on one sheet may not be on the other sheet (or visa-versa).

An example of what I need is:

Sheet1 data

A B
ID Desc
111111 This is description 1
222222 This is description 2
333333 This is description 3

Sheet2 data

A B
ID Price
222222 $5.00
111111 $7.32
333333 size:CW - $12.50;size:SW - $11.25;

Sheet3 (combined/merged data)

A B C
ID Desc Price
111111 This is description 1 $7.32
222222 This is description 2 $5.00
333333 This is description 3 size:CW - $12.50;size:SW - $11.25;

Thank you !

**MarvinP** · 04-19-2011, 01:43 PM

Hi imimin

See if the attached helps.
You will need to create a column of all ID's in Column A of sheet3 and then delete duplicates
Then look at the VLookup formulas. I hope you don't have multiple ID's the same on a single sheet.

**martindwilson** · 04-19-2011, 01:46 PM

1 copy column of ids sheet 1 to sheet 3
2 copy column of ids sheet 2 to end of those now on sheet 3
3 use advanced filter to extract unique ids
this list can now be used in sheet3 column a(delete the stuff you copied over)
now use vlookup
assuming data starts in a2 then in b2 say your widest data finishes in col h
=IF(ISERROR(VLOOKUP($A2,Sheet1!$A$2:$H$300,COLUMN(B1),FALSE)),VLOOKUP($A2,Sheet2!$A$2:$H$300,COLUMN(B1),FALSE),VLOOKUP($A2,Sheet1!$A$2:$H$300,COLUMN(B1),FALSE))
drag acros to col h and down

**alansidman** · 04-19-2011, 01:47 PM

If you have Access, this is an easy resolution. You can import both tables into Access. Join the two tables in a query and where the data matches, Access will create a report to give you the results.

If you don't have Access, then you may want to consider using MS Query which is included in Excel. It allows you to query the two tables and do the same as Access.

Since I am not sure which version of Excel you are using, I cannot direct you to the location on the tool bar or ribbon to find MS Query.

You may want to do a search on MS Query in Google for specific instructions on how to accomplish this.

Alan

**imimin** · 04-19-2011, 01:54 PM

Originally Posted by MarvinP

Hi imimin

See if the attached helps.
You will need to create a column of all ID's in Column A of sheet3 and then delete duplicates
Then look at the VLookup formulas. I hope you don't have multiple ID's the same on a single sheet.

Thanks! I am using Excel 2000, do you mind changing the file to that format?

**imimin** · 04-19-2011, 01:55 PM

Originally Posted by alansidman

If you have Access, this is an easy resolution. You can import both tables into Access. Join the two tables in a query and where the data matches, Access will create a report to give you the results.

If you don't have Access, then you may want to consider using MS Query which is included in Excel. It allows you to query the two tables and do the same as Access.

Since I am not sure which version of Excel you are using, I cannot direct you to the location on the tool bar or ribbon to find MS Query.

You may want to do a search on MS Query in Google for specific instructions on how to accomplish this.

Alan

Thanks! For the project I am on, I cannot use Accesss!