excel if function

[Shagrat]

Know my way round excel fairly well but stuck atm

Ive got a massive spreadsheet and I'm ftrying to pick certain items from it using part of a postcode field. Ive used the if function and its worked so far using:

If ("le15",<cell number>,"true") so that I can then filter just on the ones that have a true value, but now I'm trying to use OR to add a second postcode fragment but I can't get it to work and its starting to annoy me now.

I dont really want to use the inbuilt filter as there about 120 different postcodes, and its something that needs to ve done monthly so al trying to automate as much as possible.

Any ideas?

 

[Zenith.UK]

Here's how I'd do it.

A1 is where you type the postcode fragment.
B1 is the first postcode, B2, B3 etc down the column.
Enter this formula in C1:

=IFERROR(SEARCH($A$1,B1),0)

... then drag it down the C column.

SEARCH(value_to_match, cell ref) will return a 1 if the supplied string is found in the referred cell. What it won't do is return a zero if it fails, it just gives $value error.
IFERROR(cell ref, value_if_error) is a way to capture the error output of SEARCH and return a 0 instead.
Nesting the 2 together gives a 0 if the string isn't matched, or a 1 if the string is matched.

What you'd need to do then it find all the rows with a value of 1 for this formula, but I'm sure you could do that using SORT under Data, or even use Conditional Formatting on the formula column to highlight the 1's. Just highlight the column, click Conditional Formatting > Highlight Cell Rules > Equal to... then enter 1 and choose the format to highlight in.

Now whenever you update cell A1 with a new postcode fragment, the formula will output 0 or 1 in the C column depending on match, and if the result is 1 then the cell is highlighted for easy viewing.

 

[SilverHood]

Know my way round excel fairly well but stuck atm

Ive got a massive spreadsheet and I'm ftrying to pick certain items from it using part of a postcode field. Ive used the if function and its worked so far using:

If ("le15",<cell number>,"true") so that I can then filter just on the ones that have a true value, but now I'm trying to use OR to add a second postcode fragment but I can't get it to work and its starting to annoy me now.

I dont really want to use the inbuilt filter as there about 120 different postcodes, and its something that needs to ve done monthly so al trying to automate as much as possible.

Any ideas?

Use a nested if statement... check post code 1, if false, check postcode 2.....

Assuming A1 and A2 have the post codes you are after... and data is stored in A5 downwards...

=IF($A$1=A5,TRUE,IF($A$2=A5,TRUE, FALSE))

 

[Bob007]

If your postcodes are formatted correctly. XXxx xXX Xxx xX Xx xxX and so on. You could do something like.

A
L42 1R
MA16 9PQ

Add a function in B1 =FIND(" ",A1)

This will return the number of chars to the space.

In C1 add =LEFT(A1,B1-1)

This will trim A1 to the space minus the space.

Drag both down and You can filter on that.

 

[Bob007]

=IF(COUNTIF(A1,"CH42"&"*"),"True",IF(COUNTIF(A1,"L52"&"*"),"HAMSTER","CARROT"))

Original Problem.

Will look for CH42 in A1, If it finds it will display True, If not it will look for L52 in A1, if it finds that it will display HAMSTER, If both false it will display CARROT, But you just as easily next anopther if function there.

=IF(COUNTIF(A1,"CH42"&"*"),"CHESTER",IF(COUNTIF(A1,"L52"&"*"),"LIVERPOOL",IF(COUNTIF(A1,"P9"&"*"),"PORT","FALSE")))

Could go on an filll in all Geo locations within the nested =IF at the false statement.

Wiki for postcode areas in UK
http://en.wikipedia.org/wiki/List_of_postcode_areas_in_the_United_Kingdom

Someone needs to build a super if statement

 

[Shagrat]

cheers guys, should help me sort this one