Basic excel help please

Forums › IT Pro and developers › Basic excel help please

Jaxson

6770 posts

Uber Geek
+1 received by user: 602

Trusted

Topic # 126781 19-Jul-2013 14:57

Hi,

Have been trying to make this concise but it's annoying me too much now:

I want to return a Yes in a cell if an adjacent cell contains the specific text (of potentially up to 6 different 'keywords') within it's string content.

eg, A column might have a list of locations such as:

Level 1 - foyer by stairs
Level 1 - corridor by room G8
Level 1 - room 1.05 by entry

And in another column I want to flag the entry with a Yes IF the location has some warning words for me, such as "foyer" "corridor" "by room" etc.

Thus far I've got it to work with some nested statements, but it seems yuck.

eg:

=IF(ISNUMBER(SEARCH("by room",I40)),"Yes","")

=IF(ISNUMBER(SEARCH("by room",I40)),"Yes",IF(ISNUMBER(SEARCH("corridor",I40)),"Yes",IF(ISNUMBER(SEARCH("foyer",I40)),"Yes","")))

That works but it's not nice. I want to add a few more checks, and don't want to nest 5 levels down.

What I really want (I sound like the spice girls here) is:

=If (the location string in the cell next to me contains ("by room" OR "corridor" OR "foyer" OR "plant room" OR "service duct" OR "entire") then "YES" else "")

Really open to ideas here.

ubergeeknz

3344 posts

Uber Geek
+1 received by user: 1089

Trusted

Vocus

Reply # 862171 19-Jul-2013 15:05

Excel IF statements don't support multiple clauses... so you really are stuck with nesting. I can't think of a more elegant solution than the way you've already done it, TBH.

Jaxson

6770 posts

Uber Geek
+1 received by user: 602

Trusted

Reply # 862180 19-Jul-2013 15:10

ubergeeknz: Excel IF statements don't support multiple clauses... so you really are stuck with nesting. I can't think of a more elegant solution than the way you've already done it, TBH.

No, that's not what I wanted to hear :-(

I'd thought about using another statement that returns a numerical 1 or 0 perhaps, and then adding them up and displaying a Yes if the end result was larger than zero. But not sure what search statement would support that type of approach.

I can do it the way outlined above, it's just ugly looking, and more specifically not very nice if you want to add or change the search words.

(I'm aiming to bury the search words in one cell formula, rather than linking to a table of keywords somewhere else).

Thanks for the reply.

ubergeeknz

3344 posts

Uber Geek
+1 received by user: 1089

Trusted

Vocus

Reply # 862182 19-Jul-2013 15:12

Jaxson: I'd thought about using another statement that returns a numerical 1 or 0 perhaps, and then adding them up and displaying a Yes if the end result was larger than zero. But not sure what search statement would support that type of approach.

That is another way to do it, you have one cell for each search term (per row) then have it output "1" or "0". Then you sum up all the cells. Then you can hide the cells with the IF formulas in them.

TwoSeven

1293 posts

Uber Geek
+1 received by user: 144

Reply # 862186 19-Jul-2013 15:19

I didn't really read the original post too well, but try the following:-

http://office.microsoft.com/en-nz/excel-help/look-up-values-in-a-range-HP005228952.aspx

I think the second example, lookup values in a vertical list, might be a starting point.

Software Engineer

Jaxson

6770 posts

Uber Geek
+1 received by user: 602

Trusted

Reply # 862191 19-Jul-2013 15:24

ubergeeknz:

That is another way to do it, you have one cell for each search term (per row) then have it output "1" or "0". Then you sum up all the cells. Then you can hide the cells with the IF formulas in them.

Thanks,

yeah I did that originally actually but then wanted to make it as simple and concise as possible, with no hidden cells.

This warning system of locations that probably require attention will be used on multiple data sheets, so I want columns that are going to be required to be visible for copying and pasting between sheets.

It's no biggy, and all of your suggestions will work. Thanks.

jonherries

1037 posts

Uber Geek
+1 received by user: 77

Subscriber

Reply # 862193 19-Jul-2013 15:27

ubergeeknz:
Jaxson: I'd thought about using another statement that returns a numerical 1 or 0 perhaps, and then adding them up and displaying a Yes if the end result was larger than zero. But not sure what search statement would support that type of approach.

That is another way to do it, you have one cell for each search term (per row) then have it output "1" or "0". Then you sum up all the cells. Then you can hide the cells with the IF formulas in them.

Try this if you want to try the adding option:

=if(iserror(search(a1,"string",1),0,1)

You can then add then add them up like this:

=if(iserror(search(a1,"string",1),0,1)+if(iserror(search(a1,"string2",1),0,1)

Jon

jamesrt

781 posts

Ultimate Geek
+1 received by user: 246

Trusted

Subscriber

Reply # 862245 19-Jul-2013 16:27

Jaxson: =IF(ISNUMBER(SEARCH("by room",I40)),"Yes",IF(ISNUMBER(SEARCH("corridor",I40)),"Yes",IF(ISNUMBER(SEARCH("foyer",I40)),"Yes","")))

That works but it's not nice. I want to add a few more checks, and don't want to nest 5 levels down.

What I really want (I sound like the spice girls here) is:

=If (the location string in the cell next to me contains ("by room" OR "corridor" OR "foyer" OR "plant room" OR "service duct" OR "entire") then "YES" else "")

Try:

=IF(OR(ISNUMBER(SEARCH("by room",I40)),ISNUMBER(SEARCH("corridor",I40)),ISNUMBER(SEARCH("foyer",I40))),"Yes","")

It's almost the same as what you've already got; but a little easier to extend.

(I typed that by hand without checking, so may have got the ‎parentheses correct!)

NonprayingMantis

6434 posts

Uber Geek
+1 received by user: 1571

Reply # 862273 19-Jul-2013 16:39

pretty sure you can define a list of the words, and then return yes or no depending on if the string matches any of the words in the list.

From Excel help website
http://stackoverflow.com/questions/8313919/excel-search-for-a-list-of-strings-within-a-particular-string-using-array-formu

"This will return the matching word or an error if no match is found. For this example I used the following.

List of words to search for: G1:G7
Cell to search in: A1

=INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1)))
Enter as an array formula by pressing Ctrl+Shift+Enter.

This formula works by first looking through the list of words to find matches, then recording the position of the word in the list as a positive value if it is found or as a negative value if it is not found. The largest value from this array is the position of the found word in the list. If no word is found, a negative value is passed into the INDEX() function, throwing an error.

To return the row number of a matching word, you can use the following:

=MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*ROW(G1:G7))
This also must be entered as an array formula by pressing Ctrl+Shift+Enter. It will return -1 if no match is found."

Ouranos

118 posts

Master Geek
+1 received by user: 41

Reply # 862338 19-Jul-2013 19:02

Or another elegant array formula solution from StackOverflow: http://stackoverflow.com/questions/5394063/excel-if-cell-contains-1-or-more-keywords-change-value-of-a-different-cell

The important point when creating a formula like this is to document it. Explain what it does, how it works, and any traps to be aware of. A cell comment is sufficient, in most cases.

Jaxson

6770 posts

Uber Geek
+1 received by user: 602

Trusted

Reply # 862375 19-Jul-2013 20:11

Thanks everyone.

If I want to make my approach really flexible I might go the array approach, so users can define their own key words to search for.

I've achieved the result with nested checks, I just didn't think it was very elegant.