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 unit
Part(A) -- Qty(B)
a --------- 5
b --------- 3

Sheet 2 - Qty on hand
Part(A1) -- Qty(B1)
b ----------- 6
a ----------- 12

I 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

Report •


#1
November 2, 2012 at 10:09:04
✔ Best Answer
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


Ask Question