count duplicates
 

Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

count duplicates
 

hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) )))

--
isabelle



Le 2012-04-09 17:27, jt a écrit :
Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

count duplicates
 

On Mon, 9 Apr 2012 14:27:36 -0700 (PDT), jt wrote:

Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

If you want a count of unique duplicates, in other words, if the sequence:

1
2
2
2
2
1
1

should return 2 (duplicate numbers = 1 and 2), then try:

=SUM(--(FREQUENCY(A:A,A:A)1))

If this sequence should return 5 (1 is duplicated twice; 2 is duplicated thrice), then use Isabelle's formula

count duplicates
 

On Apr 9, 6:59*pm, isabelle wrote:
hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) *)))

--
isabelle

Le 2012-04-09 17:27, jt a écrit :



Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....

count duplicates
 

On Apr 9, 9:20*pm, john taiariol wrote:
On Apr 9, 6:59*pm, isabelle wrote:





hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) **)))

--
isabelle

Le 2012-04-09 17:27, jt a écrit :

Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....

what if the numbers are actually text??

count duplicates
 

On Mon, 9 Apr 2012 18:20:37 -0700 (PDT), john taiariol wrote:

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....


=SUM(--(FREQUENCY(A:A,A:A)0))

count duplicates
 

hi jt,

the first part (=COUNTA(A1:A100)) is all entries
the second part of the formula is the unique entries

=COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) )))

subtract the second part from the first gives the number of duplicates

--
isabelle



Le 2012-04-09 21:20, john taiariol a écrit :
On Apr 9, 6:59 pm, wrote:
hi jt,

=COUNTA(A1:A100)-COUNT(1/FREQUENCY(A2:A100,ROW(INDIRECT("1:"&ROWS(A2:A100)) *)))

--
isabelle

Le 2012-04-09 17:27, jt a écrit :



Hi there,
I have a column on numbers and I was wondering if I can add a formula
to the bottom of the column that would tell me how many dublicate
numbers there are.

thanks in advance for your time.

sorry but it doesn't seem to work, maybe i need to reword it, i would
like to count the number of unique entries....