Counting duplicates only once

[c0087]

Need an IF function where it will look through a range and find only single-matching duplicates. They can be in any order such as the two examples below, etc.

2	6
0	2
2	2
6	0
0	6
6	0

 

[hagia_sofia]

Hello, could you please elaborate on what is that you need including what do you mean by "single matching duplicates"? E.g. is it the aim to count every number only once, or identify duplicate values and return their list, or something else?

 

[c0087]

Hello, could you please elaborate on what is that you need including what do you mean by "single matching duplicates"? E.g. is it the aim to count every number only once, or identify duplicate values and return their list, or something else?

Yes count every number once, but if the range were this... i wouldn't want it returning a "TRUE" statement in context to some sort of if function, since there is more than just one duplicate for the number 1 and same for the number 9

1

1

9

9

9

1

 

[hagia_sofia]

My apologies for still being in the dark - how should the output differ from this:

=UNIQUE(A1:A6)

 

[c0087]

My apologies for still being in the dark - how should the output differ from this:

=UNIQUE(A1:A6)

I was thinking more along the lines of an IF or COUNTIF statement if possible... so where IF(A1:A6=x),1,0) ... That way if the range meets the duplicate criteria it will have 1,0 or TRUE FALSE

*I know that's not what the formula will look like, but i'm trying to get a basic "yes or no" output for the range*

 

[hagia_sofia]

Something along these lines (if it is not the case could you please upload how the desired output should look like):

=IF(COUNTIFS(A$1:A1,A1)=1,1,0)

 

[c0087]

Something along these lines (if it is not the case could you please upload how the desired output should look like):

=IF(COUNTIFS(A$1:A1,A1)=1,1,0)

How to change the range on that for my specific range at the moment (J96:J101) ?

 

[hagia_sofia]

=IF(COUNTIFS(J$96:J101,J96)=1,1,0)

and drag down.

 

[c0087]

=IF(COUNTIFS(J$96:J101,J96)=1,1,0)

and drag down.

1
0
6
0
6
1
	=IF(COUNTIFS(J$96:J101,J96)=1,1,0)

i have your formula in k102, and according to what I need it should be showing "1", but it's displaying "0" ... Sorry if I wasn't clear, but it's patterns like these where there are exact duplicate pairs only, but any combination. so if it was 0,1,6,6,1,0 it should still return "1"

 

[hagia_sofia]

So, how should the output loo like - like this?

1	1
0	1
6	1
0	1
6	1
1	1
9	0