Countif problem

[Nabei]

Hi there

currently facing a countif problem. Below is my sample data

45001245-1
45001245-2
45001245-3

45001248-1
45001248-2

For the duplicate data i.e. 45001245-1, -2, -3, i would like to set the data ending with -1 to value 1 and the rest to value 0

Currently using this formula
=IF(Countif(A$2:A$15,A2)=1,1,0) without the implementation of the dashes as it wouldnt work if the data has dashes.

Any help is appreciated.
Thanks in advance

 

[mfexcel]

do you data always contain only "-"?

 

[Nabei]

A combination of "-" and without. Sorry for not mentioning earlier

 

[Marcelo Branco]

Maybe...

	A​	B​
1​
2​	45001245-1​	1​
3​	45001245-2​	0​
4​	45001245-3​	0​
5​
6​	45001248-1​	1​
7​	45001248-2​	0​

Formula in B2 copied down
=IF(A2="","",--(COUNTIF(A$2:A2,LEFT(A2,SEARCH("-",A2)-1)&"*")=1))

Hope this helps

M.

 

[mfexcel]

ADVERTISEMENT

You may try the following if you always have "-??" in the end

=IF(RIGHT(A1,2)="-1",SUBSTITUTE(A1,"-",""),REPLACE(A1,FIND("-",A1),9,0))

do you data always contain only "-"?

 

[mfexcel]

For cases without "-", how do you want to treat the data?

A combination of "-" and without. Sorry for not mentioning earlier

 

[Marcelo Branco]

ADVERTISEMENT

A combination of "-" and without. Sorry for not mentioning earlier

Hadn't seen this post...

Maybe...

	A​	B​
1​
2​	45001245-1​	1​
3​	45001245-2​	0​
4​	45001245-3​	0​
5​
6​	45001248-1​	1​
7​	45001248-2​	0​
8​	12345​	1​
9​	12345​	0​
10​	AB456​	1​
11​	AB456​	0​

Formula in B2 copied down
=IF(A2="","",--(COUNTIF(A$2:A2,IF(ISNUMBER(A2),A2,LEFT(A2,IFERROR(SEARCH("-",A2)-1,LEN(A2)))&"*"))=1))

M.

 

[Nabei]

Hi, thanks for your solution.

So i meddled a bit with it.

If the data is switched around, i.e

45001245-3
45001245-1
45001245-2

the data which ends with "-3" returns a value 1 instead of 0 while the data which ends with "-1" returns a value 0

 

[Aladin Akyurek]

What are you looking for - a unique count that must equal 2 for the sample posted?

 

[Nabei]

Set a count for the data such that those that ends with "-1" (45001246-1) return a value of 1, and the rest (45001246-2, 45001246-3, 45001246-3) return a value of 0. hope that clarifies things