Find matching opposites

[Gav13]

Hi all,
First of all, great forum so cheers for the help already provided just by scanning the posts!

I've got one that couldn't find so hoping someone can help me out. I need to find matching opposite pairs by value in column B (by opposites I mean negative and positive values) which relate to the same type in column A.

There can be a number of different variables, but the important thing is that only matching pairs needs to be found - so if there is for example £13, £-13 and £13, then only the first two (or the second two) need to be counted and the extra £13 left as "no match".

Hope that makes sense and here's an example (with column A=Type, B=Value and C=Match) of what I'd need the formula to tell me:

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Type[/TD]
[TD="class: xl68, width: 64"]Value[/TD]
[TD="class: xl65, width: 64"]Match?[/TD]
[/TR]
[TR]
[TD="class: xl66"]Apples[/TD]
[TD="class: xl69, align: right"]£13.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Apples[/TD]
[TD="class: xl69, align: right"]-£13.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl68, align: right"]£13.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl68, align: right"]£12.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl68, align: right"]£12.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl68, align: right"]£15.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Pears[/TD]
[TD="class: xl69, align: right"]£14.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Pears[/TD]
[TD="class: xl69, align: right"]-£14.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl68, align: right"]-£14.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Bananas[/TD]
[TD="class: xl68, align: right"]-£15.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Bananas[/TD]
[TD="class: xl68, align: right"]-£15.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Bananas[/TD]
[TD="class: xl69, align: right"]-£15.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Bananas[/TD]
[TD="class: xl69, align: right"]£15.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Bananas[/TD]
[TD="class: xl68, align: right"]£14.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Prunes[/TD]
[TD="class: xl69, align: right"]-£18.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
[TR]
[TD="class: xl65"]Prunes[/TD]
[TD="class: xl68, align: right"]£12.00[/TD]
[TD="class: xl65"]No Match[/TD]
[/TR]
[TR]
[TD="class: xl66"]Prunes[/TD]
[TD="class: xl69, align: right"]£18.00[/TD]
[TD="class: xl66"]Match[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt"><col width="64" style="width:48pt"><col width="64" style="width:48pt"></colgroup><tbody></tbody>[/TABLE]

Hope that makes sense and happy to take any questions.

Thanks in advance.

 

[Marcelo Branco]

See if this does what you need

C2 copied down
=IF(COUNTIFS(A$2:A$100,A2,B$2:B$100,-B2)-COUNTIFS(A$1:A1,A2,B$1:B1,B2)>0,"Match","No Match")

Hope this helps

M.