countif values is unique in a range

[hluk]

hi!

tried to solve this and didn't get very far...

anyway, should be a simple problem for you excel experts

i have one column of text strings (column S). there are unique and duplicate values in this column.

what i need in column T is a formula i can drag down that only counts if the immediate row in column S is a unique value in the range S:S.

for example:
if S2 is unique in the range S:S, return 1
if S3 is unique in the range S:S, return 1
if S4 is unique in the range S:S, return 1
if S5 is a duplicate in the range S:S, return 0

i thought of using =MATCH(S2,S:S,0) and dragged down, which does tell me which rows are unique... but i don't know how to continue further


thank you so much in advanced for the help

[triver525]

=if(countif($s$2:$s$5,s2)>1,1,0)

[benishiryo]

hi hluk, try:
=IF(COUNTIF($S$2:S2,S2)>1,0,1)

[dredwolf]

wait....your condition changes at S5...Why?
otherwise a IF(COUNTIF(... sould work fine

Please Login or Register  to view this content.

drag down

Hope this helps

[dredwolf]

LOL...seems we all hit the thread right close together

[triver525]

Yeah, Range=S:S is cleaner. Good call.

[dredwolf]

it looks neater, and applies to a variable range easier, but a defined range is probably faster...the problem is, we have no idea what the actual range is, so I just used the whole column

[dredwolf]

and benishiryo's will return the first occurrence of the value(1), then puts the others as repeats(0), which may be what the OP actually wants..

[hluk]

wait....your condition changes at S5...Why?
otherwise a IF(COUNTIF(... sould work fine

Please Login or Register  to view this content.

Hope this helps

hey thanks!

i tried this... but i am not getting the result i need

here is the existing result:
2 0
2 0
3 0
3 0
4 0
4 0
4 0
4 0
4 0
10 1
11 1
12 1
13 1
14 1
15 1

here is what i hope the result to be:
2 1
2 0
3 1
3 0
4 1
4 0
4 0
4 0
4 0
10 1
11 1
12 1
13 1
14 1
15 1


sorry i didn't explain it properly... is it possible to modify it so that it counts each value once... then if has been counted once, the duplicates will get a 0?

[icestationzbra]

put this in T2 and drag-fill down:

Please Login or Register  to view this content.

[dredwolf]

use benishiryo's formula, as my last post stated, his will return the numbers you are looking for I believe

Edit-
forgot, Drag or Copy it down

Edit 2
icestationzbra's should work just as well too

[hluk]

woot!

working now. thanks!

[dredwolf]

check icestationzbra's (post #10) as well, a different approach that may work better