Assist with formula
 

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth

Assist with formula
 

Assuming your data begins in A1 and is sorted as in your example:

In B1: =COUNTIF(A:A,A1)&" of "&A1

In B2: =IF(A2=A1,"",COUNTIF(A:A,A2)&" of "&A2)
Copy B2 down

Hope this helps,

Hutch

"Soth" wrote:

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth

Assist with formula
 

In B1: =IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1)&" of "&A1,"")

copy down as far as needed


"Soth" wrote:

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth

Assist with formula
 

Great just what i'm looking for. You know, i''m interested in learning to
write...the If, the countif, and various charactor such as &, " etc..how you
came up with logic.

Can you write a plain english logical sens of this formula? just to help me
understand.

Thanks
Soth

"Teethless mama" wrote:

In B1: =IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1)&" of "&A1,"")

copy down as far as needed


"Soth" wrote:

Hi-

I'm interested in a formula medthodology rather than pivoting and advance
filter.
I'd like to look up numbers that are duplicate and more than one duplicate,
then show the result of duplicate. e.g..

A B
1 3 of 1
1
1
2 4 of 2
2
2
2
3 2 of 3
3

Thanks
Soth