I have a column that only consists of values that are at least repeated once. I need to make it return 1 if the value is repeated once, and zero if it is repeated more than once. My column only consists of values that are at least repeated once.
example:
A
A
A
B
B
C
C
C
C
C
D
D
This is the formula that I’m using:
==IF(COUNTIF($E$3:$E$111,E3)>2,"0","1")
It returns "1" when the value has only been repeated once (like B and D in above example), and returns zero for more than that. The issue though is that it returns "1" for both Bs, but i need it to do so only once.
so it looks like this now:
A0
A0
A0
B1
B1
C0
C0
C0
C0
C0
D1
D1
but I want it to look like this:
A0
A0
A0
B1
B0
C0
C0
C0
C0
C0
D1
D0
it doesn’t matter whether it returns "1" for the first B or the second, i just need it to be a single "1" to do my calculations.
What do I need to change about my formula to do that?
>Solution :
You can modify your formula by adding an additional condition to check if the current cell is the first instance of a given value. You can do this by using the MATCH function. Here’s the updated formula:
=IF(AND(COUNTIF($E$3:$E$111,E3)=2,E3<>E2),"1","0")
In this formula, E3<>E2 checks if the current cell is the first instance of the value, and the rest of the formula remains the same as before. Replace E3 and E2 with the appropriate cell references for your data.