# Solved Excel 2007 Compare to Columns then calculate

Microsoft Office excel 2007
November 2, 2012 at 08:48:26
Specs: Windows XP
 I need to find a way to compare Columns A and A1 then Calculate columns B and B1 if A and A1 match. I have 2 sheets one with the the qty or parts needed to build Unit a. And sheet 2 with the actual number parts on hand. I am trying to calculate how many units can be built based on available parts. Sheet 1 - Qty needed to build unitPart(A) -- Qty(B) a --------- 5b --------- 3Sheet 2 - Qty on handPart(A1) -- Qty(B1) b ----------- 6a ----------- 12I am trying to calculate how many units can be built based on available parts. (Example: For part a i can build 2 units (12/5). For part B I can build 2 units, etc). Is there a way to do this?

See More: Excel 2007 Compare to Columns then calculate

#1
November 2, 2012 at 10:09:04
 Try this:With a set up like this, put the formula below in Sheet2!C2 and drag it down.VLOOKUP will find the value from Sheet 2 Column A in Sheet 1 Column A and return the value from Sheet 1 Column B. The formula will then divide the value in Sheet 2 Column B by the value returned by the VLOOKUP and finally use the INT function to return the integer portion so you'll always get a whole number.Sheet 1``` A B 1 Part(A) Qty(B) 2 a 5 3 b 12 ```Sheet 2:``` A B C 1 Part(A1) Qty(B1) Full Units 2 b 6 =INT(B2/VLOOKUP(A2,Sheet1!\$A\$2:\$B\$3,2,0)) 3 a 12 ```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions