OR Formula

[greekboyuk]

Hi,

I have a spreachsheet that contains postcode data.

Some of my formulas are:
:
In cell C2 =IF(A2="","",OR(I2="CITY",B2="YES"))
In cell D2 =IF(C2=TRUE,"YES","NO")
In cell I2 =IF($A2="","",INDEX(DISTRICT,MATCH(Sheet2!A2,POSTCODES,0)))

I would like the formula in C2 if possible to display directly Yes or No and not True or false so I can delete column D.

The problem that I seem to have is in cell C2 the or function doesnt work as the value in cell I2 contains a formula and doen't match to my (I2=City). If I type manually in cell I2 the formula and type "City" then the formula correspond.

Another issue that I have is that In column D once I paste down the formula I would like to leave it blank and not display the value "No" as the cells in column A are blank.

Another question that I would like to ask is why my file is approx 3MB while it only contains a small amount of data?

I have attached a copy of my spreadsheet. If you need any additional info please let me know.

Many thanks

[daddylonglegs]

Try making C2

=IF(A2="","",IF(OR(I2="CITY",B2="YES"),"Yes","No"))

"CITY" doesn't match because in your data CITY has a "trailing space", I suggest you edit the table

[day92]

Greek,

I updated your spreadsheet. Let me know if this works for you.

[sailepaty]

Try this,

C2=IF(A2="","",IF(OR(SUBSTITUTE(I2,CHAR(160),"")="CITY",B2="YES"),"YES","NO"))

Regards

[greekboyuk]

Hello,

Thank you everyone for the help. The code that works for me is the one from sailepaty...thank you very much. how did u do it? what that substitute stands for?

Many thanks

[masteff]

CHAR(160) is a space, so it's replacing all spaces with nothing (the "" is effectively a nul text).

The only problem w/ that solution is if you're ever trying to match against something that SHOULD have a space in it. An alternative solution would be the TRIM() function which removes all extra spaces except single spaces between words.

[sailepaty]

CHAR(160) is a non-break space used for the system were you get the data from. It cannot be remove with the TRIM or CLEAN functions, that’s why we have to use the SUBSTIUTE function.

Regards

[greekboyuk]

Many thanks for your help. All of you have been great, especially sailepaty that came up with his clever formula.
I would like to say many thanks to day92 for sorting my data.


Kind regards

greekboyuk