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?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

cjcobra

Board Regular
Joined
Apr 9, 2002
Messages
193
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

Board Regular
Joined
Aug 20, 2010
Messages
143
That would not consider multiple store numbers and list each store number and UPC that were missing as compared to the Core list.....
 

jrabi

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

Watch MrExcel Video

Forum statistics

Threads
1,109,329
Messages
5,528,033
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top