Returning True Or False VALUE if a Location holds more than one Stock Code

[xdividerx]

Hey there,

As the title states, i am trying to return True or False value to a cell if a 'Location' holds more than one 'Stock Code'. I have worked out a long way around by getting 2 other cells to return "1's" and "0's" and a third cell that determines the True or False however, i do not always get the correct result.

It would be good if i could only have one formula in one cell that determines what i am after. Example below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]LINE[/TD]
[TD]Stock Code[/TD]
[TD]Location[/TD]
[TD]Does location hold more than 1 Stock Code (that is not the same stock Code)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PN001[/TD]
[TD]AB10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PN020[/TD]
[TD]AB10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PN002[/TD]
[TD]AB11[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PN003[/TD]
[TD]AB12[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PN004[/TD]
[TD]AB13[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PN030[/TD]
[TD]AB13[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PN005[/TD]
[TD]AB14[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]PN005[/TD]
[TD]AB14[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks In advance.

 

[MarcelBeug]

In C2 and copy down:

=0 < SOMPRODUCT(($B$2:$B$9=B2)*($A$2:$A$9 <> A2))

 

[MarcelBeug]

Oops, that was Dutch, apologies. Adjust SOMPRODUCT to SUMPRODUCT.

 

[xdividerx]

Thanks Marcel. Only thing i had to change was the naming convention you gave me from 'SOMPRODUCT' to 'SUMPRODUCT'.

Appreciate your help.

 

[Aladin Akyurek]

The following should be faster than the SUMPRODUCT formula:

=0 < COUNTIFS($B$2:$B$9,B2,$A$2:$A$9,"<>"&A2)

The question can be taken further...

D2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(Code<>"",IF(Location=B2,MATCH(Code,Code,0))),Ivec),1))>1

where Ivec is defined in Formulas | Name Manager as referring to:

=ROW(Code)-ROW(INDEX(Code,1,1))+1