Microsoft Excel - Validate no duplicate value in other group

Hi Everyone,

Can somebody advice me on how excel function alert or find duplicate value in other group?
Meaning value can duplicate within it group by no in others group.

Example :-
Group      Invoice#    Duplicate Yes / No?
Group A    Inv001        No   
Group A    Inv001        No

Group B    Inv002        No  

Group C    Inv001        Yes       

Remark : Group C has enter duplicate Invoice#  Inv001 . 

Can excel formual solve this ?

Thanks in advance.

Loo 

Hi Loo:

this formula ALMOST does the trick (assuming that your table starts in cell A2):

=IF(SUMPRODUCT((A$2:A$7<>A2)*(B$2:B$7=B2))>0,"Yes","No")

HOWEVER, since it looks for any entry with the same invoice number in a different group it will also flag the first two entries in your table.

Hope this helped,
Rolf

Hi Rolf,

Thanks for your help. It work great!
Even those it will flag the first two entry , once user change Invoice# then it become no duplicate.
I'll set Conditional Formatting to highlight value "YES".

Appreciate your help.

RGDS,
Loo