Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate numbers in column a and diferent values in b
My file has account numbers in column A. Some may be duplicates, some may
not. In column B their are different values for each account. In column C the duplicate numbers in A have the same value. I used conditinal formating to identify the duplicate numbers in red. Now I need to be able to sort by the values in A so that the duplicates are all together. Once I get them all together, I can write and if stmt to add the two values in column B. Is there a statement that I can use in D that would look at A and if red then return a value of x if not return a different value? Example below the acct number 4 I need to end up with 4 55 27. All the other rows are okay. A B C 1 50 20 2 10 40 4 35 27 4 20 27 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate numbers in column a and diferent values in b
There's no statement in Excel which says "if a1 is red". You need VBA for that.
However, you can accomplish the same thing with an if statement that has the same condition as your conditional format (ie, in your case a4=a3). You might also want to look at sumif. In column D, enter =sumif(A:A,A2,B:B) and copy down. Then use advanced filtering to eliminate the duplicates in A. -- Regards, Fred "Jeanne" wrote in message ... My file has account numbers in column A. Some may be duplicates, some may not. In column B their are different values for each account. In column C the duplicate numbers in A have the same value. I used conditinal formating to identify the duplicate numbers in red. Now I need to be able to sort by the values in A so that the duplicates are all together. Once I get them all together, I can write and if stmt to add the two values in column B. Is there a statement that I can use in D that would look at A and if red then return a value of x if not return a different value? Example below the acct number 4 I need to end up with 4 55 27. All the other rows are okay. A B C 1 50 20 2 10 40 4 35 27 4 20 27 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate numbers in column a and diferent values in b
hi! why don't you try =SUMPRODUCT(--(A1:A4=4),--(C1:C4=27),(B1:B4)) assuming that your data in A1:C4 -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=531116 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I ADD AMOUNTS FOR DIFERENT VALUES | Excel Worksheet Functions | |||
Sum values from diferent workbooks | Excel Worksheet Functions |