Checking duplicate codes

[[email protected]]

Hi,

Please I have a file containing list of staff and codes in a sheet named codes. In the confirmation sheet, I want excel to confirm codes that are available in the list and also check duplicates. The countif formular works fine for confirming available but is not checking duplicate. Please what I need is a formular the will also check duplicates.

Thanks.

[benishiryo]

hi there. it seems like you got no sheet named codes. you mean List? it's also good to manually key in the results you wish to see & give egs like which rows should be labelled "Duplicated". let me explain what your formula is doing currently. IF will work parts by parts, whichever comes first as priority.

so your first IF checks if A1 appears more than 0 times in List. if it appears once, great. it will return "Confirmed". if it appears twice or more, it will ALSO return "Confirmed" since it's your first IF. the IF stops checking after whichever comes first turns TRUE.

your 2nd COUNTIF seems to have the opposite arguments. you are counting how many times the List appeared in A1 instead of the other way round.

so my guess is that your 2nd IF should come first and the COUNTIF is:
=IF(COUNTIF(List!$A$1:$A$25,A1)>1,"Duplicated",IF(COUNTIF(List!$A$1:$A$25,A1)=1,"Confirmed",""))

but none is a duplicate here since it all appeared only once in List

[Teethless mama]

=CHOOSE(MIN(COUNTIF(List!A$1:A$25,A1)+1,3),"","Confirmed","Duplicate")

[Craig K.]

try this
=IF(COUNTIF(List!$A$1:$A$25,Confirmation!A1)>1,"Duplicated",IF(COUNTIF(List!$A$1:$A$25,Confirmation!A1)>0,"Confirmed",""))

[[email protected]]

Thanks guys. All the formulas worked perfectly. I appreciate.