Compare adjacent fields in adjacent rows

[Quimera]

I need to flag duplicate names in a spread sheet sorted by Surname,
Given Name (separate columns).

In other words...

IF (the surname and the given name in row(n) match the surname and given
name in the row above(n-1) or below(n+1), display "Dupe", else display "
" )

Thank you for your help.

Joan

 

[Max]

Assuming the 2 source cols are cols B and C, data in row 2 down
Put this in D2:
=IF(COUNTA(B2:C2)=0,"",IF(SUMPRODUCT((B$2:B2=B2)*(C$2:C2=C2))>1,"dupe",""))
Copy down to the last row of source data

 

[Quimera]

Sorry, that didn't seem to work. The comparison fields are text fields,
not number fields. Would that make a difference?

Thanks.

 

[Quimera]

My apologies - it does work for the row AFTER a duplicate but I also
need to show a duplicate BEFORE a row. i.e. when there are 3 or more
duplicate rows they should all show "Dupe".

Thanks

 

[Max]

Use this revised one instead, in D2:
=IF(COUNTA(B2:C2)=0,"",IF(SUMPRODUCT((B$2:B$200=B2)*(C$2:C$200=C2))>1,"dupe",""))
Copy down. Adapt the fixed ranges to suit the extent of your actual data:
B$2:B$200, C$2:C$200

 

[Quimera]

Hi Max,

Yes!!! Works beautifully. Many thanks indeed.

Joan

 

[Max]

Welcome, Joan