What would be a good formula to use in this situation?

If you have in column A:

50010 Apples
50020 Bananas
Subtotal

50110 Carrots
50120 Lettuce
Subtotal

In column B I have numbers

6
5
11

4
5
9

If I then get revised numbers without the description, lets say in
column C and D respectively:

50010 10
50020 20
50030 30
50040 40

How can I create a formula in column B that takes the revised 50010
figure and moves it into column B replacing the old figure of 6 with 10
without doing it manually because I am looking at a much larger scale.

Thanks for your help.


--
billy2willy
------------------------------------------------------------------------
billy2willy's Profile: http://www.excelforum.com/member.php...o&userid=21648
View this thread: http://www.excelforum.com/showthread...hreadid=400975

Assuming that all numbers, such as 50010, are 5 digits, try...

B1, copied to other cells...

=SUMIF($C$1:$C$100,LEFT(A1,5),$D$1:$D$100)

or

=SUMIF($C:$C,LEFT(A1,5),$D:$D)

Hope this helps!

In article ,
billy2willy
wrote:

What would be a good formula to use in this situation?

If you have in column A:

50010 Apples
50020 Bananas
Subtotal

50110 Carrots
50120 Lettuce
Subtotal

In column B I have numbers

6
5
11

4
5
9

If I then get revised numbers without the description, lets say in
column C and D respectively:

50010 10
50020 20
50030 30
50040 40

How can I create a formula in column B that takes the revised 50010
figure and moves it into column B replacing the old figure of 6 with 10
without doing it manually because I am looking at a much larger scale.

Thanks for your help.