help with a IF formula I think

[bbrimberry]

hey gang,

Im looking to compare multiple columns with a formula.

Im trying to put a formula in column K that would tell me if the cell values in column B thru column J are the same.
if B thru J are the same value or blank the formula should return Yes
if B thru J are different the formula should return No
I would like to ignore blanks.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Store	Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Match
2	1	James	James	James	James					James	Y
3	2	John	John	John	John	John			Michael	Michael	N
4	3	Robert							Robert	Michael	N
5	4	Michael			Michael	Michael	Michael	Michael			Y
6	5	Richard		Richard			Michael	Michael			N
7	6	Matthew	Matthew	Matthew	Anthony	Anthony				Anthony	N
Sheet1

 

[KevCarter]

If you have Microsoft 365

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Match
2	2	1	James	James	James	James					James	Y	Y
3	3	2	John	John	John	John	John			Michael	Michael	N	N
4	4	3	Robert							Robert	Michael	N	N
5	5	4	Michael			Michael	Michael	Michael	Michael			Y	Y
6	6	5	Richard		Richard			Michael	Michael			N	N
7	7	6	Matthew	Matthew	Matthew	Anthony	Anthony				Anthony	N	N
Sheet1

Cell Formulas
Range	Formula
M2:M7	M2	=IF(COUNTA(UNIQUE(TRANSPOSE(FILTER(C2:K2,C2:K2<>""))))=1,"Y","N")

 

[Ezguy4u]

You can never have too many formulas.

20-08-18 if Rev A.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Store	Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Match
2	1	James	James	James	James					James	Y
3	2	John	John	John	John	John			Michael	Michael	N
4	3	Robert							Robert	Michael	N
5	4	Michael			Michael	Michael	Michael	Michael			Y
6	5	Richard		Richard			Michael	Michael			N
7	6	Matthew	Matthew	Matthew	Anthony	Anthony				Anthony	N
Match

Cell Formulas
Range	Formula
K2:K7	K2	=IF(SUMPRODUCT(1/COUNTIF(B2:J2,B2:J2&""),--(B2:J2<>""))=1,"Y","N")

 

[KevCarter]

You can never have too many formulas.

20-08-18 if Rev A.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Store	Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Match
2	1	James	James	James	James					James	Y
3	2	John	John	John	John	John			Michael	Michael	N
4	3	Robert							Robert	Michael	N
5	4	Michael			Michael	Michael	Michael	Michael			Y
6	5	Richard		Richard			Michael	Michael			N
7	6	Matthew	Matthew	Matthew	Anthony	Anthony				Anthony	N
Match

Cell Formulas
Range	Formula
K2:K7	K2	=IF(SUMPRODUCT(1/COUNTIF(B2:J2,B2:J2&""),--(B2:J2<>""))=1,"Y","N")

Nice Formula! I honestly had no idea how to accomplish this without the new dynamic array functions...

 

[bbrimberry]

you guys are always incredible.
thank you!

 

[Ezguy4u]

All ways glad to lend a hand.

 

[Fluff]

Just for fun, here's another formula, if you have dynamic arrays.
=IF(COLUMNS(UNIQUE(FILTER(C2:K2,C2:K2<>""),1))=1,"Y","N")

 

[KevCarter]

Just for fun, here's another formula, if you have dynamic arrays.
=IF(COLUMNS(UNIQUE(FILTER(C2:K2,C2:K2<>""),1))=1,"Y","N")

Nice Fluff. I like the use of Columns rather than my use of Transpose. Fun with Dynamic Arrays, love it!!!