MrExcel Publishing
Your One Stop for Excel Tips & Solutions

match up and compare to find variance

Posted by Eddie on December 29, 2001 9:37 PM

Have part numbers in column "A" and quanitys in column "B" then we took inventory, now have part numbers in "C" and updated quanitys in "D". After inventory part number column "C" contains more numbers do to write in's, so it is not as simple as lining them up side by side and doing a simple formula to figure out the diff in quanity per part number. Need to match up only the part numbers which are the same in the before inventory column "A" and the after inventory column "C" and have the quanity totals before and after trail with side by side. Thanks in advance for any help

Posted by Jacob on December 29, 2001 10:05 PM


There is an easy solution.

in Column E1 type = vlookup("C1","$A$1:$B$1000,2,false) Then fill down. change the 1000 to how many rows you have. this will line up the old and new quantity numbers. In F1 type = E1=D1 this will give true if they match and false if they dont. You can then sort the list by column F to get all the trues and falses together.

Hope this helps