Formula to check if Cells contain 3 given texts

[VPLUMWOOD]

Im organising a training day where staff have 11 workshops to choose from,

They have all chosen their top 3 so I have allocated them a Session 1, Session 2 and Session 3 choice.

I want a formula at the end that tells me if Session1, Session2 and Session3 are included in the range D2:H2 so I can make sure I haven't accidentally assigned anyone Session1, Session1, Session3 etc i.e. 2 sessions with the same number. The rows wont always be in order i.e. might have session2, session1, session 3 but all together all 3 are there.

Any ideas?

Short

Mapplewell

Session2

Music

Session3

Art

Session1

DT

Gregory

Hoyland Common

Session1

EYFS

Session2

MFL

Session3

Art

 

[RobP]

Hi , welcome,

this one might be a little convoluted, but see if it works for you

=IF(COUNTIF(D6:H6,D6) + COUNTIF(D6:H6,F6) + COUNTIF(D6:H6,H6)>3,"Duplicate","OK")

 

[hrayani]

Hello,

Is this what you are looking for ??

Book3
	A	B	C	D	E	F	G	H	I	J
1	Short	Mapplewell	Session2	Music	Session3	Art	Session1	DT		Formula
2	Gregory	Hoyland Common	Session1	EYFS	Session3	MFL	Session3	Art		Duplicate Session
Sheet1

Cell Formulas
Range	Formula
J2	J2	=IF(OR(C2=E2,C2=G2,E2=G2),"Duplicate Session","All OK")

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Short	Mapplewell	Session2	Music	Session3	Art	Session2	DT	Duplicate
3	Gregory	Hoyland Common	Session1	EYFS	Session2	MFL	Session3	Art	Ok
Data

Cell Formulas
Range	Formula
I2:I3	I2	=IF(SUM(COUNTIFS(C2:H2,INDEX(C2:H2,{1,3,5})))>3,"Duplicate","Ok")

 

[VPLUMWOOD]

These all say OK but when I've changed one so there is a duplicate to test it still showed OK?

 

[hrayani]

These all say OK but when I've changed one so there is a duplicate to test it still showed OK?

Did you try this ?? make sure to change the range as per your original data

=IF(OR(C2=E2,C2=G2,E2=G2),"Duplicate Session","All OK")

 

[RobP]

can you share your sheet so we can see / try to understand whats happening please ?

Rob

 

[VPLUMWOOD]

This one works! Thank you

 

[hrayani]

This one works! Thank you

which one ??

 

[VPLUMWOOD]

can you share your sheet so we can see / try to understand whats happening please ?

Rob

Its worked using the =IF(OR(C2=E2,C2=G2,E2=G2),"Duplicate Session","All OK")