Excel reads blank cells as matching

Hello all,

I work for a home builder and we have a rule in community building that the home to the left, home to the right and the home directly across the street cannot have the same floor plan with the same exterior elevation and color scheme as the home we are building.

So, as an example if the floor plan was a "Florence with elevation "K" then no other surrounding home could be the same. However, there could be a Florence with elevation "B." - Floor plans are listed in column C and elevations are listed in a column D.

Additionally, a home cannot share the same exterior color scheme. So, if the color scheme was "SUJ" then no other surrounding home, regardless of floor plan and elevation, could have the same color scheme. - Color schemes are listed in column B.

The following formula works perfect with one exception - if I have cells without data (because no homes are built on that home site) then excel reads them as matching and "VIOLATION" populates in the cell. I need excel to ignore blank cells in the formula below.

=IF(OR(B9=B8,B9=B10,AND(C9=C8,D9=D8),AND(C9=C10,D9=D10)),"VIOLATION","")

Originally Posted by etaf

=IF(OR(B9=B8,B9=B10,AND(C9=C8,D9=D8),AND(C9=C10,D9=D10)),"VIOLATION","")

so if any cell B8, B9, B10 ,C8, C9, C10 , D8, D9, D10 = a blank then you want a blank to be returned?

you could add another IF - to test for a blank first

or you could add another clause to the OR() in your main IF (OR(

=IF( OR ( B8="", B9="", B10="" ,C8="", C9="", C10="" , D8="", D9="", D10 ="") , "", IF(OR(B9=B8,B9=B10,AND(C9=C8,D9=D8),AND(C9=C10,D9=D10)),"VIOLATION","") )

that will at least stop the evaluation if any cell is blank()

I plugged this in and I no longer receive a "VIOLATION" when there is a conflict of two matching cells. So, it doesn't show "VIOLATION" when they are blank, but it also doesn't show "VIOLATION" when it is supposed to.

Originally Posted by FDibbins

Or just start out with a quick test on 1 cell that will always be filled in for that row...

=if(A8="","",IF(OR(B9=B8,B9=B10,AND(C9=C8,D9=D8),AND(C9=C10,D9=D10)),"VIOLATION",""))

I was optimistic this would work however, I still receive the "VIOLATION" when the cells are blank.

Thank you for the response.

In the attachment you will see the formula in cell G26.

I appreciate your help and let me know if you have additional questions.

Chris

Ok, I have attached an updated spreadsheet with my original formula.

You can view the formula in cell G26.

If you type "SUM" into B26 ("SUM" matches the text in B30) or "SUJ" into B26 (matches text in B31) then you will see that G26 will turn red and say "VIOLATION." This is exactly what I want it to do.

Also if you type "1680" into C26 and "A" into D26 then G26 turns red and says "VIOLATION." This also works perfect.

Now, here is the problem: If you delete text from B30 or B31 then G26 turns red and says "VIOLATION". It is doing this because the formula is recognizing B26 and B30 as equal or B26 and B31 as equal.

Also, if you delete C30 and D30 the formula is reading the empty cells as equal and I get the unwanted "VIOLATION" response. The same is true for C31 and D31.

Let me know if this clarifies the problem for you.

Thanks - Chris

Originally Posted by etaf

=IF(OR(B26="",C26="", B30="", C30=""),"",IF(OR(B26=B30,B26=B31,AND(C26=C30,D26=D30),AND(C26=C31,D26=D31)),"VIOLATION",""))

I have added a new clause , so that if any of the cells
B26 or C26 or B30 or C30 is blank then set the cell to a blank

you can change this to use the correct cell or range of cells
to stop an empty cell = and empty cell
not fully following the code and what you are testing - but you know what should never be blank

Unfortunately this did not work correctly. It did stop the cell from having the VIOLATION when the cell is blank however, when you type a matching entry into B26 I no longer receive a VIOLATION message.

For this to work you need to define (using data) what is a "surrounding" plot..

Take these formulae:

=IF(OR(B9=B8,B9=B10,AND(C9=C8,D9=D8),AND(C9=C10,D9=D10)),"VIOLATION","")


=IF(OR(B26="",C26="", B30="", C30=""),"",IF(OR(B26=B30,B26=B31,AND(C26=C30,D26=D30),AND(C26=C31,D26=D31)),"VIOLATION",""))


The ranges are "specific" not "generic" so the formulae need amending each time to specific data ranges.

Originally Posted by JohnTopley

For this to work you need to define (using data) what is a "surrounding" plot.

Agreed, given that many of the lot numbers are not consecutive, there is nothing definite to say which lot is to the immediate left or right of any other specific lot, and even less to identify the opposite lot.