IF + COUNT IF

[jon09]

I have data in col A (item) I need to know if it is repeat or new.
the thing is, all item starts with 302 are new (N) item number, but if this item is repeating(starts with 302), I have to tag it as Repeat (R).

i have the formula to find if it is repeating.
=IF(COUNTIF($A$1:A1,A1)=1,"N","R")
but I also need to combine the formula to know if it starts with "302"
=IF(LEFT(A1,3)="302","N","R")

in this image, item starts with 11, but it says N, i need to create a formula which tells me that if item begins with 302 this is new, but if it is duplicated it should be tag as repeat. while all item that dont starts with 302 should be tagged as "R".

SORRY FOR MY ENGLISH.
hope someone can understand it and help me.

 

[jtakw]

Hi,

Not sure I understand fully, is this what you mean:

Book3.xlsx
	A	B
1	302a	N
2	11b	R
3	302c	N
4	302a	R
Sheet960

Cell Formulas
Range	Formula
B1:B4	B1	=IF(AND(LEFT(A1,3)="302",COUNTIF($A$1:A1,A1)=1),"N","R")

 

[jon09]

Hi,

Not sure I understand fully, is this what you mean:

Book3.xlsx
	A	B
1	302a	N
2	11b	R
3	302c	N
4	302a	R
Sheet960

Cell Formulas
Range	Formula
B1:B4	B1	=IF(AND(LEFT(A1,3)="302",COUNTIF($A$1:A1,A1)=1),"N","R")

hi @jtakw, YES! this is what i meant! Thank you!

 

[jtakw]

You're welcome, thanks for the feedback.