Prev: Ignore Hidden Rows and Count of distinct values
Next: Combining Cells
From: Soth on 12 Apr 2010 13:07

Result
Example

Result in col B
A B

100
100
100 3
200
300
300 2

Hi

I'd like to find the number duplicates in Column A, and place a result in
column B as shown on above in Col B. How do you create formulas?

Thanks
Soth

From: Glenn on 12 Apr 2010 13:16
Soth wrote:
> Result
> Example
>
> Result in col B
> A B
>
> 100
> 100
> 100 3
> 200
> 300
> 300 2
>
> Hi
>
> I'd like to find the number duplicates in Column A, and place a result in
> column B as shown on above in Col B. How do you create formulas?
>
> Thanks
> Soth
>


Assuming your data is in A1:A6, this array formula (commit with
CTRL+SHIFT+ENTER) will give you exactly those results:

=IF(ROW()=MAX(IF($A$1:$A$6=A1,ROW($A$1:$A$6),"")),
IF(COUNTIF($A$1:$A$6,A1)>1,COUNTIF($A$1:$A$6,A1),""),"")
From: Teethless mama on 12 Apr 2010 13:20
=IF(OR(COUNTIF($A$1:A1,A1)=1,A1=A2),"",COUNTIF($A$1:A1,A1))


"Soth" wrote:

>
> Result
> Example
>
> Result in col B
> A B
>
> 100
> 100
> 100 3
> 200
> 300
> 300 2
>
> Hi
>
> I'd like to find the number duplicates in Column A, and place a result in
> column B as shown on above in Col B. How do you create formulas?
>
> Thanks
> Soth
>
From: Bernd P on 12 Apr 2010 13:52
In B1:
=REPT(COUNTIF($A$1:A1,A1),A1<>A2)
Copy down ...

Regards,
Bernd
From: Soth on 12 Apr 2010 14:34

You're the best Bernd !! Many thanks.

"Bernd P" wrote:

> In B1:
> =REPT(COUNTIF($A$1:A1,A1),A1<>A2)
> Copy down ...
>
> Regards,
> Bernd
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Ignore Hidden Rows and Count of distinct values
Next: Combining Cells