Identifying Duplicate on a range of cells

[Shadmani]

Hi,

I am trying to use the below formula to identifty the 'Duplicate" cell content of a column from A2:A200

=IF(MAX(COUNTIF(A2:A200,A2:A200))>1,"Duplicates","No Duplicates")

If I have a 4 repeated value....then the first three is identified as "Duplicate" & the fourth one is identifed as "No Duplicate".
Now, I need the other way....

The first Value should show as "No Duplicate" & the rest three values should show as "Duplicates"

Can any one help me ?

Kind Regards
Shadmani

[Paul]

Hi Shadmani, perhaps try this in B2 and filled down:

=IF(COUNTIF($A$2:A2,A2)>1,"Duplicates","No Duplicates")

[Shadmani]

Hi Paul,

Thank you.
I think the formula is trying to check the entire range of cells with A2 only..
I am just enclosing a spread sheet of 2003, which will will give a clear picture on what I wanted. May be I should have done this earlier....

If any query, let me know

[Paul]

I think that example worksheet makes it even less clear.

Ok, you have data in A2:A1342. Many of the entries have duplicates.

If A2 is "T0880", A3 is "T0880" and A4 is "T0880", what do you want it to show in B2, B3 and B4?

If you want it to be
B2: No Duplicates
B3: Duplicates
B4: Duplicates

Then use the formula I posted earlier. It doesn't just reference A2. Once you drag/fill it down, all cell references will adjust accordingly. So in B3 it will reference $A$2:A3,A3. In B4 it will reference $A$2:A4,A4.

When you post sample workbooks, be sure to show what you currently have as well as the result you want/expect when done correctly. A "Before and After" view. That will go a long way in clearing up any miscommunication.

[Shadmani]

Thank you so much Paul...
It is working great.....

Thank you
Shadmani

[Shadmani]

Hi John,

Sorry to post my another query related to same issue.
I have attached the same sheet...with an added column

Now I need to add the total of the identical element to post it on teh first cell.

Sorry, if I have confused..But the attachment may be clear to udnerstand

[Shadmani]

attachment

[Paul]

Try using this in C2 and filled down:

=IF(COUNTIF($A$2:A2,A2)>1,"",SUMIF($A$2:$A$1342,A2,$B$2:$B$1342))

[Shadmani]

Thanks Paul. Thank you so much