software

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)gt;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.

ThanksHi!

Try this:

Assume the range in question is A1:A10

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

Biff

quot;mjquot; gt; wrote in message oups.com...
gt; hello all,
gt;
gt; i just want to know what formula will i use that will highlight the
gt; duplicated entry in a column, or shall i say, highlight the second
gt; record that appeared in the column. I did use the conditional formating
gt; with this formula: =countif(A:A, A1)gt;1 but it highlights the original
gt; and the duplicate entry. What i want is a formula that would highlight
gt; or mark the duplicate record in the column...please help.
gt;
gt; Thanks
gt;
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 quot;0quot; if the
range contains 1 quot;hitquot;, 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, quot;0quot;)}

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 advanceHi!

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:A8lt;gt;quot;quot;)/COUNTIF(A1:A8,A1:A8amp;quot;quot;))

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

quot;mjquot; gt; wrote in message ups.com...
gt; thanks sir but i have a follow-up question. what if i want my formula
gt; to return the number of duplicates found in a certain column. how will
gt; i do that? i manage to create a formula that would return quot;0quot; if the
gt; range contains 1 quot;hitquot;, how will i twist it so as i will get the no. of
gt; duplicates in a column.
gt;
gt; my formula is: {=if(countif('Sheet1'!$A$1:$EG$300, A3)=1, quot;0quot;)}
gt;
gt; EG: column 1 contains 1,2,2,3,4,5,5,5; since 1, 3, and 4 appeared once
gt; in the series, i will get '0' in the target column where i paste my
gt; formula. As for 2 and 5, notice that there are duplicates. what i want
gt; now is add a formula from the original so i could get the values 2 and
gt; 3, since the nos. 2 and 5 appeared twice and thrice in the column
gt; respectively.
gt;
gt; I hope someone could help me nest my formula. thanks in advance
gt;