Find missing data from one sheet compared to another sheet and list missing data

[jrabi]

I have a file with two sheets:

Sheet1 is a store file with Store numbers in column A, UPC in column B and UPC Name in column C.

Sheet1
Store # UPC UPC NAME
123 2222 soap
123 2221 Candy
124 2222 soap
124 3333 Soda


Sheet2 is a CORE list on a separate sheet, Column A is the product status, Column B is the UPC an Column C is the UPC Name.

Sheet2
Status UPC UPC NAME
Y 2222 soap
Y 2221 Candy
N 3333 Soda

What I would like is to list the missing UPC's on a separate sheet -Sheet3 and I would like to see the following on Sheet3:

Sheet3
Store # UPC UPC NAME
124 2221 Candy
123 3333 Soda


column A to list the store number(from Sheet1, columnA)
column B to list the UPC that is missing (from Sheet1, Column B as compared to all UPC list on Sheet2, Column B)

column C to list the UPC name from Sheet1, column C.

In the example above store 124 did not have UPC 2221 Candy and store 123 did not have UPC 333 Soda and these 2 UPC are on the Core list in Sheet2.

Is there a formula for this vs doing manual everytime?

 

[jrabi]

What about Miss Excel???

 

[cjcobra]

you could make a master list, and do a =IF(ISERROR(VLOOKUP(a1,sheet1 a1:a50,1,0),a1,"")

This will look to see if each item is in your main sheet, if not, it will return itself on your master sheet.

 

[jrabi]

That would not consider multiple store numbers and list each store number and UPC that were missing as compared to the Core list.....

 

[jrabi]

Actually CJCobra, this formula does not work at all, I have the below formula which will find a match:

=IF(SUM(COUNTIF(INDIRECT("'"&{"stores"}&"'!A:A"),A2)),"match","NO MATCH")

But I have thousands of stores with hundreds of UPC's, so to avoid all the manual picking, I need the final output to be

Sheet3
Store # ---UPC-----UPC NAME
124------- 2221----Candy
123 -------3333----Soda

Based on the list on two separate sheets:

Sheet 1 is the store list with each stores list of UPC's to compare to sheet 2. Any UPC on the store list not on sheet 2 I need to list in sheet 3 as above.

Sheet1
Store #------UPC-----UPC NAME
123--------- 2222----soap
123 ---------2221----Candy
124 ---------2222----soap
124 ---------3333----Soda


Sheet2 is a CORE list on a separate sheet, Column A is the product status, Column B is the UPC an Column C is the UPC Name.

Sheet2
Status------- UPC-----UPC NAME
Y-------------2222----soap
Y-------------2221----Candy
N-------------3333----Soda

 

[jrabi]

On a separate page