Using Excel 2003.

I have two columns for each month in a spreadsheet. Column A is the item
code and column B is the cost/item for last month. i then have in column C,
item codes and in column D cost/items for this month. Columns A and C do not
match entirely, because the same items may not have been touched each month.
Each may contain unique items, but some are common. I need to do the
follwing:

In column E, I need each row to look at what is in column C, see if it is
also in column A, and then show the decrease/increase of cost/item
(difference between columns B and D).

MARCH APRIL CHANGE IN COST
A B C D E
A134 $1 A043 $2 (SHOW NOTHING OR $0)
A558 $3 A134 $2 (SHOW +$1)
A6563 $2 A6563 $2 (SHOW $0)
A779 $2 A694 $4 (SHOW NOTHING OR $0)
A965 $8 A779 $1 (SHOW -$1)

I hope someone can help -- is making me crazy!

copy this formula down

=if(isna(vlookup(c2,a$2:b$500,2,0)),0,d2-vlookup(c2,a$2:b$500,2,0))

"Eightball" wrote:

> Using Excel 2003.
>
> I have two columns for each month in a spreadsheet. Column A is the item
> code and column B is the cost/item for last month. i then have in column C,
> item codes and in column D cost/items for this month. Columns A and C do not
> match entirely, because the same items may not have been touched each month.
> Each may contain unique items, but some are common. I need to do the
> follwing:
>
> In column E, I need each row to look at what is in column C, see if it is
> also in column A, and then show the decrease/increase of cost/item
> (difference between columns B and D).
>
> MARCH APRIL CHANGE IN COST
> A B C D E
> A134 $1 A043 $2 (SHOW NOTHING OR $0)
> A558 $3 A134 $2 (SHOW +$1)
> A6563 $2 A6563 $2 (SHOW $0)
> A779 $2 A694 $4 (SHOW NOTHING OR $0)
> A965 $8 A779 $1 (SHOW -$1)
>
> I hope someone can help -- is making me crazy!

Hi,

Try this

=sumif($A$1:$A$5,C1,$B$1:$B$5)-D1

Now copy this down

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eightball" <(E-Mail Removed)> wrote in message
news:9CB84BD2-E6F5-48E2-B14B-(E-Mail Removed)...
> Using Excel 2003.
>
> I have two columns for each month in a spreadsheet. Column A is the item
> code and column B is the cost/item for last month. i then have in column
> C,
> item codes and in column D cost/items for this month. Columns A and C do
> not
> match entirely, because the same items may not have been touched each
> month.
> Each may contain unique items, but some are common. I need to do the
> follwing:
>
> In column E, I need each row to look at what is in column C, see if it is
> also in column A, and then show the decrease/increase of cost/item
> (difference between columns B and D).
>
> MARCH APRIL CHANGE IN COST
> A B C D E
> A134 $1 A043 $2 (SHOW NOTHING OR $0)
> A558 $3 A134 $2 (SHOW +$1)
> A6563 $2 A6563 $2 (SHOW $0)
> A779 $2 A694 $4 (SHOW NOTHING OR $0)
> A965 $8 A779 $1 (SHOW -$1)
>
> I hope someone can help -- is making me crazy!

Slight variation to the formulae you have already been given.

Format column E using:

+$#,##0.00;-$#,##0.00

If column A will never repeat any values then use the formula:

=$D1-SUMIF($A1:$A5,$C1,$B1:$B5)

otherwise use the formula:

=IF(ISNA(MATCH($C1,$A1:$A5,0)),0,$D1-VLOOKUP($C1,$A1:$B5,2,0))





"Eightball" <(E-Mail Removed)> wrote in message
news:9CB84BD2-E6F5-48E2-B14B-(E-Mail Removed)...
> Using Excel 2003.
>
> I have two columns for each month in a spreadsheet. Column A is the item
> code and column B is the cost/item for last month. i then have in column
> C,
> item codes and in column D cost/items for this month. Columns A and C do
> not
> match entirely, because the same items may not have been touched each
> month.
> Each may contain unique items, but some are common. I need to do the
> follwing:
>
> In column E, I need each row to look at what is in column C, see if it is
> also in column A, and then show the decrease/increase of cost/item
> (difference between columns B and D).
>
> MARCH APRIL CHANGE IN COST
> A B C D E
> A134 $1 A043 $2 (SHOW NOTHING OR $0)
> A558 $3 A134 $2 (SHOW +$1)
> A6563 $2 A6563 $2 (SHOW $0)
> A779 $2 A694 $4 (SHOW NOTHING OR $0)
> A965 $8 A779 $1 (SHOW -$1)
>
> I hope someone can help -- is making me crazy!