How to compare two Excel files for differences

[i8ur4re]

Hi guys/gals, I need your help. I have 2 excel worksheets. One is inventory the second is products.

This is what the Inventory sheet looks like, minus 6 other columns:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]100006
[/TD]
[TD]3 PC BAR SETBLK WEATHEED; BARK
[/TD]
[/TR]
[TR]
[TD]100018
[/TD]
[TD]3PCS BISTRO SET,CARAMEL ;BRWN/CHARCOAL, 30"RDX30"
[/TD]
[/TR]
[TR]
[TD]100028
[/TD]
[TD]BAR TABLE~;30.00"Lx30.00";Wx42.00"H
[/TD]
[/TR]
</tbody>[/TABLE]


This is what the Product sheet looks like, minus 12 other columns:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Brand Name
[/TD]
[TD]UPC
[/TD]
[TD]Condition
[/TD]
[/TR]
[TR]
[TD]100006
[/TD]
[TD]Coaster
[/TD]
[TD]######
[/TD]
[TD]Brand New
[/TD]
[/TR]
[TR]
[TD]100018
[/TD]
[TD]Coaster
[/TD]
[TD]######
[/TD]
[TD]Brand New
[/TD]
[/TR]
[TR]
[TD]100026
[/TD]
[TD]Coaster
[/TD]
[TD]######
[/TD]
[TD]Brand New
[/TD]
[/TR]
</tbody>[/TABLE]


Like the sheets are named, Inventory gives me the current inventory in stock while the Product sheet gives me detailed information about any given item.

As you can see, Inventory has a "Product ID" field in A1 just like the Products sheet.

I am trying to list products that are currently in stock, so for me to do that, I need to look at the Inventory sheet, unfortunately its tedious work, 9000+ products.

I would like to create a 3rd sheet if possible that will find all the "Product ID's" in the Inventory sheet and merge them with the Product sheet. If you look at the bold numbers in the tables, you will see that they don't match, that's because the product is not available in the inventory report or vice versa. This is due to the warehouse we receive products from, its a master product list for over 15 warehouses. So our inventory is limited to Chicago which is part of the Inventory sheet.

At the end of the day, I just want to use the Product ID numbers that are listed in the Inventory sheet while maintaining the valuable information in the Product sheet such as UPC, Brand, Condition, Price, Etc. As for the information that does not match, I would like to create a 4th table that allows me to view what Product ID's did not match in the Inventory sheet.

I hope that made sense, I am at a dead end with this, any help would be appreciated.

 

[Johnny C]

Best place to start, create a 3rd sheet. Copy all the Product IDs from both sheets into column A starting at row 2. Select all the data, then under the Data tab, click remove Duplicates and follow the instructions. You now have a master list of both.

In column B enter this into cell B2

=IFERROR(MATCH($A2,Inventory!$A$1:$A$20000,0),"Not in Inventory")

where A2:A20000 is the cells with Product ID in

In column C enter this into cell C2

=IFERROR(MATCH($A2,Product!$A$1:$A$20000,0),"Not in Product")

where A2:A20000 is the cells with Product ID in

In Column D enter this into cell D2

=IF(OR($B2="Not in Inventory",$C2="Not in Product"),"Item missing in one sheet","Item in both sheets")

Then copy this down to the bottom of the data in column A.

If you need to build a master table beyond column D, use the OFFSET function. The values in columns B/C - if they aren't "Item missing in ..." are the number of the row where the product ID is on each sheet.

To get the inventory data in column E use
=IF(ISNUMBER($B2),OFFSET(Inventory!$B$1,$B2,0),"No data") in E2
To get the product data in cols F-H use
=IF(ISNUMBER($C2),OFFSET(Product!$B$1,$C2,0),"No data") in F2
=IF(ISNUMBER($C2),OFFSET(Product!$C$1,$C2,0),"No data") in G2
=IF(ISNUMBER($C2),OFFSET(Product!$D$1,$C2,0),"No data") in H2

obviously you'll need to change the columns/rows as appropriate.

You can use Autofilters on the master table to see where you're missing data or (for example) to list any items that contain a piece of text