hello all,

i just want to know what formula will i use that will highlight the
duplicated entry in a column, or shall i say, highlight the second
record that appeared in the column. I did use the conditional formating
with this formula: =countif(A:A, A1)1 but it highlights the original
and the duplicate entry. What i want is a formula that would highlight
or mark the duplicate record in the column...please help.

Thanks

Hi!

Try this:

Assume the range in question is A1:A10

Select the range A1:A10
Goto FormatConditional Formatting
Formula is: =COUNTIF(A$1:A1,A1)1
Click the Format button
Select the style(s) desired
OK out

Biff

"mj" wrote in message
oups.com...
hello all,

i just want to know what formula will i use that will highlight the
duplicated entry in a column, or shall i say, highlight the second
record that appeared in the column. I did use the conditional formating
with this formula: =countif(A:A, A1)1 but it highlights the original
and the duplicate entry. What i want is a formula that would highlight
or mark the duplicate record in the column...please help.

Thanks

thanks sir but i have a follow-up question. what if i want my formula
to return the number of duplicates found in a certain column. how will
i do that? i manage to create a formula that would return "0" if the
range contains 1 "hit", how will i twist it so as i will get the no. of
duplicates in a column.

my formula is: {=if(countif('Sheet1'!$A$1:$EG$300, A3)=1, "0")}

EG: column 1 contains 1,2,2,3,4,5,5,5; since 1, 3, and 4 appeared once
in the series, i will get '0' in the target column where i paste my
formula. As for 2 and 5, notice that there are duplicates. what i want
now is add a formula from the original so i could get the values 2 and
3, since the nos. 2 and 5 appeared twice and thrice in the column
respectively.

I hope someone could help me nest my formula. thanks in advance

Hi!

Try this:

# of duplicates = total entries - unique entries

Based on the sample you posted:

1,2,2,3,4,5,5,5

With that data in the range A1:A8:

=COUNT(A1:A8)-SUMPRODUCT((A1:A8<"")/COUNTIF(A1:A8,A1:A8&""))

Returns 3

I used the COUNT function since the data was all numeric. If the data is
text or both use the COUNTA function.

This assumes that there are no formula blanks within the range.

Biff

"mj" wrote in message
ups.com...
thanks sir but i have a follow-up question. what if i want my formula
to return the number of duplicates found in a certain column. how will
i do that? i manage to create a formula that would return "0" if the
range contains 1 "hit", how will i twist it so as i will get the no. of
duplicates in a column.

my formula is: {=if(countif('Sheet1'!$A$1:$EG$300, A3)=1, "0")}

EG: column 1 contains 1,2,2,3,4,5,5,5; since 1, 3, and 4 appeared once
in the series, i will get '0' in the target column where i paste my
formula. As for 2 and 5, notice that there are duplicates. what i want
now is add a formula from the original so i could get the values 2 and
3, since the nos. 2 and 5 appeared twice and thrice in the column
respectively.

I hope someone could help me nest my formula. thanks in advance