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

 

[Aladin Akyurek]

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

Are you then going to sum those 1 and 0's to arrive at a count?

 

[mfexcel]

but you said there will be cases without "-"; and the sequence may not be in ascending order...
could you pls state your requirements in one go?

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

 

[Nabei]

Just to return the value 1 or 0. Not going to sum them up

 

[Nabei]

but you said there will be cases without "-"; and the sequence may not be in ascending order...
could you pls state your requirements in one go?

sorry,

1.For cases without "-" returns a value of 1
2.cases with "-", those that ends with "-1" returns a value of 1, others ("-2", "-3") return value of 0
3.Cases with "-" may not necessarily be in ascending order, "-2" may appear first and "-1" might appear several columns later.
4.duplicates (45001234-1, 45001234-2) may or may not be grouped together

apologies for not explaining in detail. Happened to find out several problems halfway trying out different formulas

 

[Aladin Akyurek]

Just to return the value 1 or 0. Not going to sum them up

X	Y
45001245-1	1
45001245-2	0
45001245-3	0
45001248-1	1
45001248-2	0

<COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4693" width=132><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

B2, control+shift+enter, not just enter, and copy down:

=IF($A2="","",IF(1-ISNUMBER(MATCH(LEFT($A2,FIND("-",$A2&"-")-1),
  LEFT($A$1:A1,FIND("-",$A$1:A1&"-")-1),0)),IF(RIGHT($A2,2)+0=-1,1,0),0))

 

[mfexcel]

seems that the suffice -1, -2, -3, is already an indication of duplicates, isn't it?

If that's the case, something like the following may work:

45001245-1	1
45001245-2	0
45001245-3	0
45001234	1
45001235	1
45001248-2	0
45001248-1	1
45001245-4	0

<tbody>
</tbody>

In B2
=IF(ISERROR(FIND("-",A1)),1,IF(RIGHT(A1,2)="-1",1,0))

Not sure if this suits your case.

sorry,

1.For cases without "-" returns a value of 1
2.cases with "-", those that ends with "-1" returns a value of 1, others ("-2", "-3") return value of 0
3.Cases with "-" may not necessarily be in ascending order, "-2" may appear first and "-1" might appear several columns later.
4.duplicates (45001234-1, 45001234-2) may or may not be grouped together

apologies for not explaining in detail. Happened to find out several problems halfway trying out different formulas

 

[Nabei]

seems that the suffice -1, -2, -3, is already an indication of duplicates, isn't it?

If that's the case, something like the following may work:

45001245-1	1
45001245-2	0
45001245-3	0
45001234	1
45001235	1
45001248-2	0
45001248-1	1
45001245-4	0

<tbody>
</tbody>

In B2
=IF(ISERROR(FIND("-",A1)),1,IF(RIGHT(A1,2)="-1",1,0))

Not sure if this suits your case.

works like a charm! Thank you so much! Something new learnt today

 

[mfexcel]

You are welcome. Glad to help.

works like a charm! Thank you so much! Something new learnt today