Formula for repeats + certain criteria

[Yin00]

Is it possible to have an formula for this. I want the last column to have a formula that will spit out 'Repeat' if the value in column J matches that of another in column J. But only if they both have 'FG' in column E. Otherwise, it should say 'In Process'. So the only row that should say Repeat is 6 because it has the same number in J as row 2 and they both have FG in E.

 

[lrobbo314]

How about this?

Red Car.xlsm
	E	F	G	H	I	J	K	L
2	FG1					123		REPEAT
3	FG2					124		PROCESS
4	FG3					125		REPEAT
5	A					123		PROCESS
6	FG4					123		REPEAT
7	B					123		PROCESS
8	A					125		PROCESS
9	FG5					126		PROCESS
Sheet9

Cell Formulas
Range	Formula
L2:L9	L2	=CHOOSE((COUNTIF($J$2:$J$9,J2)>1)*(LEFT(E2,2)="FG")+1,"PROCESS","REPEAT")

 

[Yin00]

How about this?

Red Car.xlsm
	E	F	G	H	I	J	K	L
2	FG1					123		REPEAT
3	FG2					124		PROCESS
4	FG3					125		REPEAT
5	A					123		PROCESS
6	FG4					123		REPEAT
7	B					123		PROCESS
8	A					125		PROCESS
9	FG5					126		PROCESS
Sheet9

Cell Formulas
Range	Formula
L2:L9	L2	=CHOOSE((COUNTIF($J$2:$J$9,J2)>1)*(LEFT(E2,2)="FG")+1,"PROCESS","REPEAT")

Sorry, my initial question was wrong, row 2 should also say repeat as you have. But why does row 4 also say repeat? The only time 125 is mentioned again, there is no FG

 

[Saikachi]

College grad and new here. I wanted to try this out so please excuse my mess.

 

[Yin00]

College grad and new here. I wanted to try this out so please excuse my mess.

These both seem to work well! Thank you!

 

[Peter_SSs]

These both seem to work well!

You say they both work but they produce different results on the same data.
Which is the correct one for what you want?

@Saikachi
Welcome to the MrExcel board!
When suggesting formulas, please post the actual formula rather than just a picture of it so readers can easily copy it to test. There are several ways ..

1. Simply copy and paste =IF(IF(LEFT(E2,2)="FG",COUNTIFS(J$2:J2,J2,E$2:E2,"*FG*"),1)>1,"Repeat","Process")
   
   
2. Use the formula code tags at the top of the Reply window. It is the one that says fx/XLS

   =IF(IF(LEFT(E2,2)="FG",COUNTIFS(J$2:J2,J2,E$2:E2,"*FG*"),1)>1,"Repeat","Process")

   
   
3. Post a mini sheet with data and formulas using XL2BB like in post #2

 

[Yin00]

You say they both work but they produce different results on the same data.
Which is the correct one for what you want?

@Saikachi
Welcome to the MrExcel board!
When suggesting formulas, please post the actual formula rather than just a picture of it so readers can easily copy it to test. There are several ways ..

1. Simply copy and paste =IF(IF(LEFT(E2,2)="FG",COUNTIFS(J$2:J2,J2,E$2:E2,"*FG*"),1)>1,"Repeat","Process")
   
   
2. Use the formula code tags at the top of the Reply window. It is the one that says fx/XLS

   =IF(IF(LEFT(E2,2)="FG",COUNTIFS(J$2:J2,J2,E$2:E2,"*FG*"),1)>1,"Repeat","Process")

   
   
3. Post a mini sheet with data and formulas using XL2BB like in post #2

When I tested it, it looks like one of the formulas only reads up. So only row 6 would show repeat. The other formula gives repeat for both row 2 and 6. I think the second one that gives repeat on both will be used. But this gives me an option to see what my team prefers! Thank you!

 

[Peter_SSs]

OK, thanks for the clarification.