Count occurrences and sort them

[1Ronin]

Hello,


I have a sheet with few hundreds of lines and I need to sort them based on the occurrence of serial number in column A.
Some numbers appear only one time, but I have some that appear 2 or more times.
I need to have a sort like this in another column:
- serial numbers one time = 0
- duplicate 1 = 1
- duplicate 2 = 2
...
I use the formula "=COUNTIF($A$2:$A2, $A2)" to count the occurrence of each serial, but I can't sort between first test (0 and 1 from above) or second test (0 and 2 from above).
There are only 1,2,3... and can't discriminate.
I know it's something easy but my knowledge is limited.
Thank you for your time.


Regards,

 

[steve the fish]

If ive read correctly you need the first and seconf occurance? Use this and sort:

=IF(COUNTIF($A$2:A2,A2)<3,1,0)

 

[1Ronin]

Hi Steve,

Formula is not working. Is giving me 1 almost all time.
I need something like this:
SerialNo1 = 0
SerialNo2 = 0
SerialNo3 = 1
SerialNo3 = 2
SerialNo4 = 0
SerialNo5 = 1
SerialNo5 = 2
SerialNo5 = 3
SerialNo6 = 0
...

0 - part pass from 1st test
1 - fail 1st test
2(3) - pass 2nd(3rd) test

These are different parts tested at different times. Some of them pass from first time, some on 2nd (3rd) time, so I need to discriminate (pass/fail) from first vs second test.
Normally I should have only 2 tests, but in some occasions could be more.
I hope is more clear now.


Thanks for your time.

 

[Fluff]

How about
=IF(COUNTIF(A$2:A$10,A2)=1,0,COUNTIF(A$2:A2,A2))

 

[1Ronin]

Hi Fluff,

As it is written is working partially. But is working when I increase the range:
=IF(COUNTIF(A$2:A$1000,A2)=1,0,COUNTIF(A$2:A2,A2))


In the end I found another solution using below formula (not so elegant as yours):
=IFS(COUNTIF($A$2:$A3000,$A2)<=1,0,COUNTIF($A$2:$A3000,$A2)>1,COUNTIF($A$2:$A2,$A2))

Both formulas works Ok and give same results. See below results:
Test times / Output

[TABLE="width: 97"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Thanks all for help.


Regards,

 

[Fluff]

Glad we could help & thanks for the feedback