0

I need to compare to columns to determine if a part number (distinct) is in both columns, then determine the variance in number of parts. In this example, I'd like to have Column D find it's distinct part number in Column B, then subtract the Qty found in Column E from the Qty found in Column C to determine the Variance (Column F).

A       B          C               D         E          F
1    Part #       QTY           Part #      QTY       Variance   
2     1234        8              4584        20         0
3     5678        10             1123        25         0
4     1123        25             9652        10         5
5     1245        10             1789        45        (5)
6     1356        5              9652        15         0
7     1789        40             1234        7          1
8     4584        20             1245        10         0 
9     9652        15             1356        4          1
2
  • Part # QTY Part # QTY Variance 1234 8 4584 20 0 5678 10 112 25 0 1123 25 9652 10 5 1245 10 1789 45 -5 1356 5 9652 15 0 1789 40 1234 7 1 4584 20 1245 10 0 9652 15 1356 4 1 – user267887 Oct 29 '13 at 16:28
  • Can you sort columns B and D and then compute the difference? – darthbith Oct 29 '13 at 16:49
1

Based on this site: http://office.microsoft.com/en-us/excel-help/look-up-values-in-a-range-HP005228952.aspx

You can place the following formula in cell F1 and fill it down column F:

=INDEX(C$1:C$8,MATCH(D1,B$1:B$8,0))-E1

This function does several things. The first is the MATCH part. MATCH tells Excel to find the value in cell D1 somewhere in the range B1:B8. The $ symbol before the numbers is so when you copy the cell to the rest of the column, it still matches the proper rows. MATCH returns the position of the value in B1:B8 that matches D1. Because the third input to MATCH is 0, it looks for the exact value. Next is the INDEX, which returns the value of a given row from a given column. Here, the column is given by C1:C8, because these are the values we're interested in operating with. The row number is provided by the MATCH function. Finally, we subtract the value in column E.

1
  • You're welcome! If it works for you, can you accept it as the answer? There's a green checkmark below the up and down arrows on the left, just click that. Thanks! – darthbith Oct 30 '13 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.