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.


Jaxson

8041 posts

Uber Geek

Trusted

#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
ubergeeknz
3344 posts

Uber Geek

Trusted
Vocus

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



Jaxson

8041 posts

Uber Geek

Trusted

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

ubergeeknz
3344 posts

Uber Geek

Trusted
Vocus

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



TwoSeven
1623 posts

Uber Geek

Subscriber

  #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
   (the practice of real science, engineering and management)
A.I.  (Automation rebranded)
Gender Neutral
   (a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

 

 ...they/their/them...


Jaxson

8041 posts

Uber Geek

Trusted

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

jonherries
1395 posts

Uber Geek

Trusted
Subscriber

  #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

jamesrt
1609 posts

Uber Geek

ID Verified
Trusted
Lifetime subscriber

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

 
 
 

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.
NonprayingMantis
6434 posts

Uber Geek


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

Ouranos
118 posts

Master Geek


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

Jaxson

8041 posts

Uber Geek

Trusted

  #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





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









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.