Compare adjacent fields in adjacent rows

  • Thread starter Thread starter Quimera
  • Start date Start date
Q

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
 
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
 
Sorry, that didn't seem to work. The comparison fields are text fields,
not number fields. Would that make a difference?

Thanks.
 
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
 
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
 
Back
Top