Tagging Duplicates 3

[NumberCrunchingMonky]

I have a list of people and codes.
There are duplicates in the people list (no greater than one duplicate per name), but the codes are unique. So, John Doe could be listed only twice, while having two unique codes.

How can I alter the following formula so that it returns the other code (instead of "Duplicate&quot.
- The field containing LAST NAME begins at A2, CODES begin at B2, and the formulas begin C2? (Assume the field with last names is named "LASTNAME&quot.

Formula: IF(COUNTIF(LASTNAME,A2)>1,"Duplicate","&quot

Thanks,

NCM

 

[luceze]

It looks as if you just need to reference your code cell instead of using "Duplicate".

For example:
Formula: IF(COUNTIF(LASTNAME,A2)>1,B2,"&quot

HTH,
Eric

 

[NumberCrunchingMonky]

Thansks for your help Luceze, but it returns the same code. I'm looking for a return of the other code. For example:

A B C
1 NAME CODE FORMULA
2 John Doe 12345 23456
3 John Doe 23456 12345

Regards,
NCM

 

[straybullet]

=IF(COUNTIF(lastname,A2)>1,B2,C2) seems to work the way you describe

 

[GlennUK]

This will do it :

=IF(COUNTIF(LASTNAME,A2)>1,IF(ISNA(MATCH(A2,$A$1:A1,0)),"",INDEX($B$1:B1,MATCH(A2,$A$1:A1,0)))&IF(ISNA(MATCH(A2,A3:$A$12,0)),"",INDEX(B3:$B$12,MATCH(A2,A3:$A$12,0))),"&quot

Change references to row 12 to be the row after the end of the list.

Put this formula in cell C2, and copy down as far as necessary.

Cheers, Glenn.

 

[NumberCrunchingMonky]

Thanks a bunch luceze, stray and Glenn.

Monky