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

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

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

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