Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


6733 posts

Uber Geek
+1 received by user: 593

Trusted

Topic # 126781 19-Jul-2013 14:57
Send private message

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.

Create new topic
3343 posts

Uber Geek
+1 received by user: 1089

Trusted
Vocus

  Reply # 862171 19-Jul-2013 15:05
Send private message

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.



6733 posts

Uber Geek
+1 received by user: 593

Trusted

  Reply # 862180 19-Jul-2013 15:10
Send private message

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.

3343 posts

Uber Geek
+1 received by user: 1089

Trusted
Vocus

  Reply # 862182 19-Jul-2013 15:12
Send private message

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.

1235 posts

Uber Geek
+1 received by user: 126


  Reply # 862186 19-Jul-2013 15:19
Send private message

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

 




6733 posts

Uber Geek
+1 received by user: 593

Trusted

  Reply # 862191 19-Jul-2013 15:24
Send private message

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.

1029 posts

Uber Geek
+1 received by user: 74

Subscriber

  Reply # 862193 19-Jul-2013 15:27
Send private message

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

765 posts

Ultimate Geek
+1 received by user: 243

Trusted
Subscriber

  Reply # 862245 19-Jul-2013 16:27
Send private message

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!)

6434 posts

Uber Geek
+1 received by user: 1571


  Reply # 862273 19-Jul-2013 16:39
Send private message

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."

118 posts

Master Geek
+1 received by user: 41


  Reply # 862338 19-Jul-2013 19:02
Send private message

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.



6733 posts

Uber Geek
+1 received by user: 593

Trusted

  Reply # 862375 19-Jul-2013 20:11
Send private message

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.


Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.