# Cross checking 2 tables

• Hi everyone! I need help with this... BADLY!

I have two sheets (sheet1 and sheet2).

Sheet1 has Column2 with "product ID codes"
Sheet2 has Column1 with "product ID codes", Column2 with Quantity, Column3 with Costs

BUT: there are some "product ID codes" from sheet1 that I dont have on sheet2 and vice versa.

What I need to do is: Get rid of duplicates from both "product ID codes" (from sheet1 and sheet2). Then I need to cross check and add Quantities and Costs (from sheet2) to only the "Product ID codes" that are Matching! This needs to be added to Sheet1's version.

Thanks
M

• Re: Cross checking 2 tables

This is how it looks (see attachment)...

I really want to learn how to do this as I will be cross checking a lot of spread sheets in the future.

Thank you for your help - will be more then welcome! forum.ozgrid.com/index.php?attachment/35753/

## Files

• Re: Cross checking 2 tables

Hi Minja,
Nice to meet you in ozgrid.
Would you have a look attatched?
Regards, junho

## Files

• Re: Cross checking 2 tables

Hi there.

Firstly thank you so much for helping me out.

So this is what I have done...

1. I have put both columns that have Product ID codes next to each other
2. deleted duplicates using Advanced filter /unique thingy
3. used =IF(COUNTIF(\$A\$2:\$A\$932,\$B2),\$B2,"") to see if which ID codes from exSheet2 have a match with exSheet1
4. used =IF(COUNTIF(\$B\$2:\$B\$1114,A2),VLOOKUP(A2,\$B\$2:\$B\$1114,1),"") to adjust the exSheet2 codes in the same rows where exSheet1 codes are.

Now i'm left with adjusting Quantities and LCs to the ID codes that are in step 4....

Anyone??? pweeze??

• Re: Cross checking 2 tables

I have been trying to use IF and LOOKUP functions but its not working

• Re: Cross checking 2 tables

Hi Minja,
Try this function.
=IF(COUNTIF(\$B\$2:\$B\$1114,A2)>0,VLOOKUP(A2,\$B\$2:\$B\$1114,1,0),"")
Regards, junho

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!