Formula to check if 3 cells have identical value

[Captain Smith]

Data is in cells A1 to A5. What formula can I enter in B1 that will return "YES" if any three of these cells have identical data? If 2,4 or 5 of these cells have identical data return nothing. If none of the cells have identical data, return nothing.

Thank you.

 

[PaddyD]

=stdev(range)=0

or

if(stdev(range)=0,"Yes","")

 

[Captain Smith]

Not understandng. How does Standard Deviation apply here?

Thanks..

 

[eliW]

Array enter the formula as shown:

Eli

Book1
	A	B	C	D
1	A	Yes
2	B
3	C
4	B
5	B
6
Sheet1

 

[PaddyD]

"How does Standard Deviation apply here?"

From the excel help file entry for stdev():

"The standard deviation is a measure of how widely values are dispersed from the average value (the mean)."

i.e. if there's no deviation / dispersal, the values must be the same.

 

[Captain Smith]

Thank you both.

 

[mahen77]

HI,

Try this .... hope fully this will resolve your problem.

=IF(COUNTIF($A$1:$A$5,A1)>1,"Yes","")

Thanks
mahen

 

[shad0w4life]

Would there be any way to modify the formula to go

if A1 matches C1
and if B1 matches D1 THEN Say Yes

EG

Cat 1 Kat 1
Cat 1A Cat 1
Cat2 a Cat 2
Cat2 4 Cat 1A



so then in column A1 it would look at Cat and then B1 look at 1 and then go look at cells in C for cat and D for 1...it would find a match and put a YES (I can always insert a blank row in after and have it right beside)
so Cat 1 and cat 1A would have a yes rest would say no as they didn't fully match. and not case sensitive.

 

[Aladin Akyurek]

Would there be any way to modify the formula to go

if A1 matches C1
and if B1 matches D1 THEN Say Yes

EG

Cat 1 Kat 1
Cat 1A Cat 1
Cat2 a Cat 2
Cat2 4 Cat 1A



so then in column A1 it would look at Cat and then B1 look at 1 and then go look at cells in C for cat and D for 1...it would find a match and put a YES (I can always insert a blank row in after and have it right beside)
so Cat 1 and cat 1A would have a yes rest would say no as they didn't fully match. and not case sensitive.

=(A1=C1)*(((B1=D1)+(LEFT(B1,1)=LEFT(D1,1)))>0)

 

[shad0w4life]

Err sorry I think I was too vauge


I want to take A1 and compare it to EVERY cell in Column C

if it finds and EXACT match(Not case sensitive) then I want it to compare B1(A1's Version Number) to D#(the column C version number that is DIRECTLY to it's right)

so if A1=C# AND B1=D# then "YES MATCH!"


Edit: On a fun note what you posted makes a oh I dunno 6 hour job take about 30 mins :P


go down the column till it says 0 fix it and then fill down till next zero!

Gona expect a huge workload soon if I keep this up hehe