how to match and pick excel data

I have data in two separate Excel files.

Excel 1: column:> Location - Code - weight


Excel 2 : column:> Location - Code - weight


I want to dump data in a new Excel file for common Location in these Excel file but code and weight will be from Excel 2 only.


Please see this Sample data:
----------------

Excel 1:
-----------
Location ------Code-----Weight
Paris 12 40
Germany 15 25
Africa 17 15


Excel 2:
-------------
Location ------Code-----Weight
Paris 78 34
Spain 15 25
Madrid 24 54


As you see Paris is a common location in this two excel , so I'll pick this location ...also I'll pick code and weight from Excel 2 .

so new Excel will dump this data ..
New Excel:
---------------------
Location ------Code-----Weight
Paris 78 34


I have more than 15000 data in these excels which I need to compare and pick and have to put in a new excel through this logic. Please suggest me a workaround


I am a Excel user ...not an excel programmer. Can you suggest a solution from end user's perspective.

I'm using Office Version: 2007

You don't really need a macro for this, the steps below shouldn't take more than a couple of minutes.

1) Open both documents
2) Copy the Excel2 sheet into the Excel1 workbook as a new sheet, let's assume the results are Excel1 = Sheet1 and Excel2 = Sheet2

3) Now on sheet2 in empty column D put this formula in D2
=ISNUMBER(MATCH(A2, Sheet1!A:A, 0))
4) Copy that D2 cell down the entire data set, a series of TRUE/FALSE answers will appear.

5) Highlight row1 and turn on the Data > Filter
6) Filter column D for TRUE
7) Highlight the visible rows and copy them to a new workbook

8) Close the two original workbooks without saving any changes you made, thus the original workbooks are pristine

Originally Posted by JBeaucaire

You don't really need a macro for this, the steps below shouldn't take more than a couple of minutes.

1) Open both documents
2) Copy the Excel2 sheet into the Excel1 workbook as a new sheet, let's assume the results are Excel1 = Sheet1 and Excel2 = Sheet2

3) Now on sheet2 in empty column D put this formula in D2
=ISNUMBER(MATCH(A2, Sheet1!A:A, 0))
4) Copy that D2 cell down the entire data set, a series of TRUE/FALSE answers will appear.

5) Highlight row1 and turn on the Data > Filter
6) Filter column D for TRUE
7) Highlight the visible rows and copy them to a new workbook

8) Close the two original workbooks without saving any changes you made, thus the original workbooks are pristine

what this part is doing ? Sheet1!A:A ?

If you followed the instructions it would become clear visually. We trying to "match" the value in sheet2's cell A2 to anywhere in Sheet1's column A. If a match occurs, it returns a number to the MATCH() function. Since the MATCH() function is wrapped inside an ISNUMBER() test, that results in TRUE if a number occurs for the match, and a FALSE if no number appears because there is no match.

Once you copy that formula down you will clearly see the TRUE/FALSE results for the matches. Then an Autofilter is used to copy the matches (TRUE) to another sheet.

Originally Posted by JBeaucaire

You don't really need a macro for this, the steps below shouldn't take more than a couple of minutes.

1) Open both documents
2) Copy the Excel2 sheet into the Excel1 workbook as a new sheet, let's assume the results are Excel1 = Sheet1 and Excel2 = Sheet2

3) Now on sheet2 in empty column D put this formula in D2
=ISNUMBER(MATCH(A2, Sheet1!A:A, 0))
4) Copy that D2 cell down the entire data set, a series of TRUE/FALSE answers will appear.

5) Highlight row1 and turn on the Data > Filter
6) Filter column D for TRUE
7) Highlight the visible rows and copy them to a new workbook

8) Close the two original workbooks without saving any changes you made, thus the original workbooks are pristine

This works to some extent but not complete...I want to pick the Code and Weight from other sheet whenever a match is TRUE....I am stuck here now.

I have put formula in Sheet1 ....I want Code and Weight to pick up from Sheet 2 for matching locations and want to make a new workbook....how do I do this part ?


You know why I'm doing it ? its because ...Sheet1 has old data and Sheet2 has latest data....I'm trying to get the latest data from Sheet 2 but only for matching locations in both sheets.

at the end of this work ...I want to have a final sheet of [locations(matching) , codes (latest data) and weights(latest data)]

I want to apply formula in Sheet1 only...because there are some other data (which I did not post for simplicity) which I require in new workbook

Hope you have got my objective.