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

jrabi

Board Regular
Joined
Aug 20, 2010
Messages
143
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
That would not consider multiple store numbers and list each store number and UPC that were missing as compared to the Core list.....
 
Upvote 0
Actually CJCobra, this formula does not work at all, I have the below formula which will find a match:

HTML:
=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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top