Solved

Excel need to copy a cell if not containing one or more keywords

Posted on 2011-02-14
12
255 Views
Last Modified: 2012-05-11
I need to check a column for particular keywords (more than one) , if found I need to change the content of that cell in a new column if not to just copy it to the new column.

I was hoping to use the IF and FIND statement to do this but if if the FIND fails than it puts #VALUE! in the corresponding cell in the new column.
0
Comment
Question by:yvan_vallee
  • 7
  • 4
12 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
VLOOKUP can often be used for searching for specific cell value in more than one cell.  If each cell contains one keyword, that may be the way to go.

Can you describe your problem a bit more?

It sounds like you need IF(VLOOKUP(keyword1,...),[add to cell next to result], VLOOKUP(keyword2,...))
0
 

Author Comment

by:yvan_vallee
Comment Utility
I want to have a column that checks another column and fills it with either the content of the original column OR if the column contains a keyword to put a default value ( to replace the contant of the original column).

I belive a VLOOKUP is more to check if it matches a (look-up) table content first column. I don't want to do a look-up table (unless there is no choice). It is really to change the cell content in the new column if it matches the keyword checking (if not simply copy the original content).

Thank you,
Yvan
0
 
LVL 2

Expert Comment

by:geoffkk
Comment Utility
I will assume the cell containing a keyword has just the keyword, ie not a keyword in part of a string.
Try
create a list of the keywords in a suitable place, eg other worksheet. Name the range kword.
If the data to be checked in in column A starting at A3, use the following formula in B3:
=IF(ISNA(VLOOKUP(A3,kword,1,FALSE)),A3,"default")
where "default" is whatever you want to put there.

If you wanted a different default value for each keyword you could use snother column in the keyword table and replace "default" by vlookup(A3,kword,2,false)
Geoff
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
so it still sounds something like this:

=IF(ISERR(VLOOKUP(keyword, KeyWordList, 1, FALSE)), originalvalue, keyword))

KeyWordList can be set up as a named range of keywords somewhere.

So if you have your original value in column A (original value), put this formula in column B, and reference the keyword (maybe from column C).

How many keywords do you want to match for each cell?  If more than 3, then this will be tough to code so maybe that's why you want to go a different way?  If so, then VBA/macros would be the way to go.
0
 

Author Comment

by:yvan_vallee
Comment Utility
Hi,
Just to clarify here, the keyword is part of a text in the cell. So I was hoping that the function FIND could be use part of the cell formula to check for that keyword part of the cell.

Is it possible to add FIND in the formula so it can check the cell content for the keyword?

Thank you.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
The VLOOKUP won't work with FIND (as far as I know) and the FIND won't work on multiple cells.

One solution, if you have a fixed range of cells containing keywords, is to combine the keywords into a single cell (using the CONCATENATE function), with a delimiter that won't appear in any of the text, such as "|".  Then the FIND will work.

E.g.
if D1:D7 contain keywords
and C1 contains some default word
and B1 contains the word you want to see if it's a keyword

set cell E1=CONCATENATE("|", D1, "|", D2, "|", D3, "|", D4, "|", D5, "|", D6, "|", D7, "|")
set cell A1=IF(FIND("|" & B1 & "|", $E$1 & B1 & "|") > LEN(D1), $C$1, B1)

What this does is combines all the keywords into a single cell E1, with separators.
And A1 will try to find the keyword in B1 inside that list of keywords; if found, it will use the keyword otherwise it will use the default.

...or are you looking for the opposite: to see if any keyword is located anywhere within a sentence?
If so, you'll need some VBA or a more complex arrangement of cell (one per sentence) to indicate whether each keyword is located in cell and if so then mark it, then vlookup for the first one that matches and show it.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Here could be a sample custom function that might handle what you're trying to do.

Put the code below into a new VBA module (from Excel, Alt-F11 then menu Insert | Module)

 
Function FindKeyword(KeywordRange As Range, SentenceToCheck As String, DefaultValue As String) As String
    Dim strResult As String
    Dim objCell As Range
    
    strResult = DefaultValue
    For Each objCell In KeywordRange
        If InStr(SentenceToCheck, objCell.Value) > 0 Then
            strResult = objCell.Value
            Exit For
        End If
    Next objCell
    
    FindKeyword = strResult
End Function

Open in new window


Then back in Excel, add a formula such as this:

=FindKeyword(A1:F1,H1,"not found")

Where A1:F1 is the range of keywords (or maybe C1:C7, etc)
and H1 is the sentence that may contain a keyword
and "not found" is the default text or a cell with that text.
0
 

Author Comment

by:yvan_vallee
Comment Utility
Hi,
This (I used the VB script) is "almost" doing what I need to do. It does return the keyword in the cell with the formula if the tested cell contains one of the keywords.

For me, the ultimate solution is to replace this keyword with another name like if it would be a VLOOKUP. So finding a keyword in the tested cell , if found, to substitute it with a another word or string in the cell having the formula. I hope this is clear enough.

Is this possible to do in the VB script or in the cell formula itself?

Thank you!
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
I guess you can add an extra parameter to the function to indicate what you'd like to show if found.
Function FindKeyword(KeywordRange As Range, SentenceToCheck As String, DefaultValue As String, SubstituteValue As String) As String
    Dim strResult As String
    Dim objCell As Range
    
    strResult = DefaultValue
    For Each objCell In KeywordRange
        If InStr(SentenceToCheck, objCell.Value) > 0 Then
            strResult = SubstituteValue
            Exit For
        End If
    Next objCell
    
    FindKeyword = strResult
End Function

Open in new window

0
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
Comment Utility
Usage of the above function:

=FindKeyword(A1:F1,H1,"not found", "found keyword!")
0
 

Author Closing Comment

by:yvan_vallee
Comment Utility
Thank you that works fine and to provide the VB script code as I am not familar much with it.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Glad to help.  Excel functions can be a wonderful thing :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now