If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
comparing data in excel
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! |
#2
|
|||
|
|||
comparing data in excel
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! |
#3
|
|||
|
|||
comparing data in excel
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" wrote in message ... 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! |
#4
|
|||
|
|||
comparing data in excel
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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|