Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to flag the items that net off each other
Hi Everyone,
I have a problem and even after trying out several things I am not able to solve it. I have a table as below: Code Book Amount 9885F3 ABC 10 9885F3 ABC -10 9885F3 ABC 10 9885F3 PQR -10 7864D1 XYZ 100 7864D1 XYZ -100 465D45 BOMS 50 465D45 BOMS -50 465D45 BOMS 50 465D45 BOMS -50 465D45 BOMS 50 I need to flag the items that belong to the same code and same book and have equal and opposite amounts. In the above case, it should flag item # 1, 2 and should not fall the # 3 and 4. Likewise it should flag # 5, and #6 and then it should flag the next four items and leave the last one. Any help on this will be much appreciated. Regards, -AG |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to flag the items that net off each other
I copied your data to A1 (with a header row)
In D2 I used IF(AND(A2=A3,B2=B3,C2+C3=0),"flag","") Copied this down the Colum If seems to do what you want best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "AG" wrote in message ... Hi Everyone, I have a problem and even after trying out several things I am not able to solve it. I have a table as below: Code Book Amount 9885F3 ABC 10 9885F3 ABC -10 9885F3 ABC 10 9885F3 PQR -10 7864D1 XYZ 100 7864D1 XYZ -100 465D45 BOMS 50 465D45 BOMS -50 465D45 BOMS 50 465D45 BOMS -50 465D45 BOMS 50 I need to flag the items that belong to the same code and same book and have equal and opposite amounts. In the above case, it should flag item # 1, 2 and should not fall the # 3 and 4. Likewise it should flag # 5, and #6 and then it should flag the next four items and leave the last one. Any help on this will be much appreciated. Regards, -AG |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to flag the items that net off each other
Can there be more than two rows for a combination of Code and Book?
Your sample has three for 9885F3 & ABC. If there are only two then you can use a PIVOT table and filter on Sum of amount equal to zero.. "AG" wrote: Hi Everyone, I have a problem and even after trying out several things I am not able to solve it. I have a table as below: Code Book Amount 9885F3 ABC 10 9885F3 ABC -10 9885F3 ABC 10 9885F3 PQR -10 7864D1 XYZ 100 7864D1 XYZ -100 465D45 BOMS 50 465D45 BOMS -50 465D45 BOMS 50 465D45 BOMS -50 465D45 BOMS 50 I need to flag the items that belong to the same code and same book and have equal and opposite amounts. In the above case, it should flag item # 1, 2 and should not fall the # 3 and 4. Likewise it should flag # 5, and #6 and then it should flag the next four items and leave the last one. Any help on this will be much appreciated. Regards, -AG |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to flag the items that net off each other
Hi Guys,
Thank you for the replies. Yes there can be more than two rows for the same code and the Book and that is why I feel it is challenging. If there were exactly two items then I could have used the SUMIF formula. And the two lines that are netting off each other may not come one after the other. They can be anywhere in the dataset. Let me know if that helps. Regards, Amol |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flag Help | Excel Discussion (Misc queries) | |||
SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
Flag 1 in 5 entries | Excel Worksheet Functions | |||
Flag for Follow Up | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |