Compare adjacent fields in adjacent rows
 

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

Compare adjacent fields in adjacent rows
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:thl%j.317$i74.205@edtnps91...
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

Compare adjacent fields in adjacent rows
 

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

Thanks.

"Max" wrote in message
...
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:thl%j.317$i74.205@edtnps91...
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

Compare adjacent fields in adjacent rows
 

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


"Quimera" wrote in message
news:P_s%j.404$i74.309@edtnps91...
Sorry, that didn't seem to work. The comparison fields are text
fields, not number fields. Would that make a difference?

Thanks.

"Max" wrote in message
...
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:thl%j.317$i74.205@edtnps91...
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

Compare adjacent fields in adjacent rows
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:kmt%j.407$i74.332@edtnps91...
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

Compare adjacent fields in adjacent rows
 

Hi Max,

Yes!!! Works beautifully. Many thanks indeed.

Joan

"Max" wrote in message
...
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:kmt%j.407$i74.332@edtnps91...
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

Compare adjacent fields in adjacent rows
 

Welcome, Joan
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:joD%j.445$cV.139@edtnps92...
Hi Max,

Yes!!! Works beautifully. Many thanks indeed.

Joan