How to compare two Excel files for differences

i8ur4re

Board Regular
Joined
Mar 1, 2015
Messages
97
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:

Product ID
Description
100006
3 PC BAR SETBLK WEATHEED; BARK
100018
3PCS BISTRO SET,CARAMEL ;BRWN/CHARCOAL, 30"RDX30"
100028
BAR TABLE~;30.00"Lx30.00";Wx42.00"H

<tbody>
</tbody>


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


Product ID
Brand Name
UPC
Condition
100006
Coaster
######
Brand New
100018
Coaster
######
Brand New
100026
Coaster
######
Brand New

<tbody>
</tbody>


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. :)
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
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
Code:
=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
Code:
=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
Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,215
Members
416,230
Latest member
jdaitchman

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
Top