Cross referencing lots of data

[twofootgiant]

Hi guys

I have two sheets, each with roughly the same format..

About 5000 lines of data 3 colums thick...column A is part number, B is description and C is price. each line makes up one product.

There are more products on sheet one than sheet two, but I only want to keep the products which are on both...i want to do this by matching the part numbers.

I need to cross reference these with eachother....lets say a row was set like this....123, Cheese, $10. I need to move all of the products with a part number which is already included in sheet two's data onto sheet 3, So if 123 was included in sheet 1 and sheet 2 as a part number: that line from sheet 1 would be moved to sheet 3.....any ideas? cos im stumped.

[royUK]

The quickest way would probably be to copy all to one sheet, then run some code to remove the duplicates.

[JBeaucaire]

Part Numbers in column A on both sheets? Sheet1, on column D1 (or D2 if row 1 is labels) enter this formula:

=MATCH(A1,Sheet2!A:A,0)

Drag that column down the whole data set. Highlight then entire 4 columns of data including the new column and sort by D, all the ones with an #N/A error can be deleted.

You could use an exact range, too:

=MATCH(A1,Sheet2!$A$1:$A$5500,0)

[TyeJae]

Using VBA:

This will compare the two pages and put the like part numbers in a new page, so you don't loose any information.

Please Login or Register  to view this content.

[twofootgiant]

thanks , that works great.

How would i adapt this so that after finding the matching product it would display the part number and description from the 2nd sheet and the price from the 1st...to be displayed the same was in the 3rd sheet?

[JBeaucaire]

Who are you talking to? We would each have a different answer based on our suggestions.

[JBeaucaire]

For mine, after you've sorted the data and deleted all the #N/A entries, enter this formula ON TOP of description on Sheet1, in cell B2:

=VLOOKUP(A2,Sheet2!$A$2:$B$5000,2,FALSE)

Drag that down over the original descriptions and they will be replaced by the ones from Sheet2.

Now, highlight the entire column B, Copy, Edit>PasteSpecial>Value.

Done.

[twofootgiant]

Oh sorry, i was talking to TyeJae

Thanks for the help

[TyeJae]

Here is the requested change. I just changed a couple 1's to 2's

Please Login or Register  to view this content.

[twofootgiant]

Ok im using this code to cross reference my data and display the matching product on sheet 3

Please Login or Register  to view this content.

That works fine. But as well as this code doing what it does I need it to make the part numbers in sheet 2 column A which dont match with any part numbers in Sheet 1 colum A B moved to sheet4 column A.

So that the end result will be: I will have compared the data from sheets 1 and 2 and placed the mathcin sets in sheet 3. The products which are in sheet 2 but not sheet one will b moved to sheet 4.

Any help?

[twofootgiant]

bump ! !