| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| compare two text and if equal copy adjacent data | Excel Worksheet Functions | |||
| When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
| compare adjacent cells and calculate averages | Excel Worksheet Functions | |||
| summing values from adjacent column with refrence from adjacent column | Excel Discussion (Misc queries) | |||
| compare contents of two adjacent columns | Excel Worksheet Functions | |||
Linear Mode
