How do you compare the data from two sheets. and highlight Differences

[stephkc24]

Hi,

I have two sheets in a workbook,

one is user entry (Sheet 1), and has the following Columns

Item Description	Quantity On Hand	Quantity on Order
paper clips	200	1000
Pens	50	0

<tbody>
</tbody>

And then I have another sheet (Database Extract) that has data that is from a database export. The data is the in the same format as above.

What I would like to do is have away to highlight the differences between the two sheets.

Example:

• if i have a item on the Data Extract tab that i don't find in the Sheet one i would like to highlight the name in Red.
• If the item is found i want to check teh Quantity on hand and on order to make sure they match. If they don't match also highlight in Red.

How would I go about doing something like this?

Thanks

 

[SteveO59L]

have a look at vlookup()

 

[stephkc24]

have a look at vlookup()

How would i go about using Vlookup? I understand how to use VLOOkup if i wanted to compare column a in both sheet and maybe return a value of column C when it finds it. But for what I'm looking for I want to find the Item name then compare column B in both sheet and Column C as well and then highlight the differences so that I know what are the discrepancies., and they can be addressed.

 

[Youngdand]

IF you combine the vlookup with an ifna statement, you can check if it exists, on the sheet, and return the quantity, or anything you like if its missing, then set up conditional formatting on the data sheet, to highlight values where the quantity does equal.

so, the formula would be ifna(vlookup," cell value to lookup", original sheet range or named range,column number to return counting from beginning of range,false),"missing")

then set up some conditional formatting in the cells on your data sheet eg if originalqty <> newqty highlight red and is newqty = "missing" highlight green.