Excel 2000 - Formula to show different content in cells

[dsmith910]

I have a workbook in Excel 2000 on Windows XP

On one of the sheets I have a series of 6 cells per row, only 4 of which will have content at any one time. This content must be different in each cell.

Is there a way I could enter a formula to check that each of these filled 4 cells are different? I.e. if cells B2, C2, D2, E2 contain say ‘Border’, ‘Cross’, ‘Terrier’, ‘GSD’ then since these are all different cell A2 would return ‘OK’. However if cells B2, C2, D2, E2 contained say ‘Border’, ‘Border’, ‘Terrier’, ‘GSD’ cell B2 would return ‘Wrong’.

Since there are generally over 100 rows in each spreadsheet and probably an average of 4 sheets per week being raised I would then like cell A1 to check whether all the cells in column A = OK, if true return a value of ‘OK’ if false return a value of ‘Wrong’.

Any thoughts?

Thanks

Doreen

 

[Cresby]

cell A2 (eg)
=A3+(if(b2=c2,1,0)+if(b2=d2,1,0)+if(b2=e2,1,0)+if(c2=d2,1,0)+if(c2=e2,1,0)+if(d2=e2,1,0))

and copy cells down, then in cell A1

=if(A2<>0 ,False,True)

a bit cumbersome and not ideal to expand to more columns but a start.

ANd you can do a similar thing from sheet to sheet on cell A1

Enjoy

 

[Zathras]

This is a little simpler:

A2: =IF(OR(B2=C2,B2=D2,B2=E2,C2=D2,C2=E2,D2=E2),"Wrong","OK")

 

[dsmith910]

Hi Guys

Thanks a lot - will try out tonight and let you know how I go on.

Doreen

 

[Cresby]

Zathras
Much better, but I usually try long IF's each in a column of their own for error checking then combine when satisfied.
Your method only goes half-way how will it get an answer into cell A1? I still think the IF to test the cell (ad nauseum) above is the answer. My ruse also counts the number of matches (should it ever be needed)

 

[Zathras]

Interesting points...
This could go into cell A1 to give the over-all result. (I missed that part of the spec.)

A1: =IF(COUNTIF(A2:A200,"Wrong")= 0,"All OK",COUNTIF(A2:A200,"Wrong")&" Wrong")

It displays the count of how many are still wrong. As to which method is preferable, I generally give the nod to the least code balanced by the easiest to maintain. In this case it's probably a toss-up.