Excel Find text in column and create a YES flag in new column

Question

Hi I have an excel column (header - Detail) which contains a long text I want to find Certain Text or phrase and when it is find new inserted column in respective row should be flagged as YES.

Second Objective is to rename the header with Text which was entered by User

I have 'Details' column only User runs a Macro and message box appears to ask what he want to find then whatever user enters it is looked into Details column and a new column is inserted with YES Flag. so for example in three runs of macro user first enter Peach Then Banana And lastly Apple and final result look like below picture.

can anyone solve the issue via macro-vb or vba

my code so far

Dim colNum As Integer
colNum = ActiveSheet.rows(1).Find(What:="Details", LookAt:=xlWhole).Column

     ActiveSheet.Columns(colNum + 1).Insert


ActiveSheet.Cells(1, colNum + 1).Value = "VARIABLE Entered by user"
    Dim colRange As Range

EndSub

i cannot rely on formula as Column 'Details' changes the positions in entire class teacher computers

Currently I am stuck with the Partial Match query 'WIN' flags the words 'WIDOW' even though 'N' is not present I wish If I can get YES flags when all of my query characters are matched in a word ( even if they are in big complete word)

enter image description here

Hi sapna, do you definitely want to use VBA? This could be solved using formulae if you wished? — maxhob17, Mar 03 '17 at 12:00
Have you attempted anything yourself? There are multiple ways of achieving this, including a standard Excel formula, a UDF, or a routine using `Search`. If you attempt something then we can guide you but it's quite an open question. — CLR, Mar 03 '17 at 12:02
I am creating a toolbar and column ranges to 1000s sometimes, help will be great — sapna, Mar 03 '17 at 12:02
my attempt are in very slow i dont know how to get column name and find with YES flag — sapna, Mar 03 '17 at 12:06
if any of the answers helped you out, please mark it as answer — tretom, Mar 03 '17 at 14:24

Ron Rosenfeld · Accepted Answer · 2017-03-03T13:31:26.660

2

Here is a VBA solution using Regular Expressions, which allow for the simple setting of word boundaries, so as to differentiate, for example berry from blueberry.

If speed is an issue, this can be sped up by working in VBA arrays.

I will leave it to you to format the column headers.

EDIT: Regex made case-insensitive

Option Explicit
Sub FlagWord()
    Dim R As Range, WS As Worksheet
    Dim RE As Object
    Dim C As Range, D As Range
    Dim S As String
    Dim I As Long, J As Long

S = InputBox("Enter desired word")

'Current filled in range
Set WS = Worksheets("sheet1")
With WS
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    Set R = R.Resize(columnsize:=.Cells(1, .Columns.Count).End(xlToLeft).Column)
End With

If Not S = "" Then

'If S not present then add column
With WS.Rows(1)
    Set C = .Find(what:=S, after:=.Cells(1, 1), LookIn:=xlValues, _
        lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
End With

'Add column if not already present
    If C Is Nothing Then
        Set R = R.Resize(columnsize:=R.Columns.Count + 1)
        R(1, R.Columns.Count) = S
    End If

End If 'no new column if S is blank

'do the word match
'Clear the data area
With R
    .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).ClearContents
End With

'fill in the data
'use regex to allow for easy word boundaries
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = False 'only need a single match
    .ignorecase = True
    For Each C In R.Columns(1).Offset(1, 0).Resize(R.Rows.Count - 1).Cells
        For Each D In R.Rows(1).Offset(0, 1).Resize(columnsize:=R.Columns.Count - 1).Cells
        .Pattern = "\b" & D.Text & "\b"
            If .test(C.Text) = True Then
                R(C.Row, D.Column) = "YES"
            End If
        Next D
    Next C
End With

End Sub

edited Mar 03 '17 at 13:31

answered Mar 03 '17 at 13:17

Ron Rosenfeld

53,870
7
28
60

Thanks a ton can you make it non case sensitive – sapna Mar 03 '17 at 13:25
@sapna Just change the `.ignorecase` – Ron Rosenfeld Mar 03 '17 at 13:26
OH dear it removes the entire data of other columns which are after first column, specifically it should search only 'DETAILS' column and not to touch other column. at the end of existing column it should enter the YES flags – sapna Mar 03 '17 at 13:28
@sapna It should be writing those columns back. It does not remove the headers, and it rechecks everything, based on the column headers, in case you have changed something in one of the strings in column 1. If that is not happening, there is something different about what you've presented and what your real data is like. – Ron Rosenfeld Mar 03 '17 at 13:32
Hi Ron I need a little help- as i notice code match entire word can we get FLAG when all the characters are found e.g. query - WOM it gives flag even WOMEN or WOMAN is present in column, thanks for help – sapna Mar 04 '17 at 13:46
@sapna. I don't understand what you mean. – Ron Rosenfeld Mar 04 '17 at 13:49
I mean that text user find is matched entirely e.g WOM will flag WOM only not WOMEN – sapna Mar 04 '17 at 13:56
@sapna remove word boundary tokens. `.Pattern = D.Text` – Ron Rosenfeld Mar 04 '17 at 14:01
@sapna Don't really need regex if just looking for partial matches. Could also use InStr. – Ron Rosenfeld Mar 04 '17 at 14:03
but it match one character e.g. WOM flags WINDOW too – sapna Mar 04 '17 at 14:08
@sapna WOM at column header should not match WINDOW in text string – Ron Rosenfeld Mar 04 '17 at 14:20
i have edited question with image hope it will clear current situation – sapna Mar 07 '17 at 16:42
@sapna I don't see any change in the image. Suggest you upload a workbook to some sharing site that shows the problem, including the VBA macro you are using. And a clear written out explanation of what you want and what you are getting. – Ron Rosenfeld Mar 07 '17 at 17:13
thanks image is not shown as i have less pints file is at this lin :- http://jmp.sh/YG18KcN – sapna Mar 08 '17 at 02:22
@sapna I had asked you to upload a workbook *including the VBA macro you are using*. Without that, I cannot tell where the problem lies. There is no macro/VBA in the workbook you uploaded. – Ron Rosenfeld Mar 08 '17 at 12:40
@sapna You have completely removed the line that sets the Regex pattern. I suggest you read through [How to use Regular Expressions in VBA](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) – Ron Rosenfeld Mar 08 '17 at 17:53
thanks it helped, anything to color green only YES flag? – sapna Mar 09 '17 at 01:17
@sapna Conditional Formatting – Ron Rosenfeld Mar 09 '17 at 01:54

score 1 · Answer 2 · answered Mar 03 '17 at 12:24

1

Here is the solution @maxhob17 was talking about using Excel formulas only and not VBA:

The formula in cell B2 is:

=IF(ISERROR(SEARCH(B$1,$A2)),"","YES")

Copy the formula over and across to the other cells and you are done.

answered Mar 03 '17 at 12:24

Ralph

9,284
4
32
42

Thanks Ralph but I am looking for macro which prompt for text as I cannot know which text user wants. further Column Details shifts the places user to user – sapna Mar 03 '17 at 12:42
@sapna: check my answer. if sheet structure is constant, and you activate the sheet (or reference the sheet), it should work for you – tretom Mar 03 '17 at 12:48

tretom · Answer 3 · 2017-03-03T13:17:40.530

1

With your sheet structure using VBA. The solution is definitely not general, but might be good as a start

EDIT: Now it shows an input window for a search term, and selects the (last - if there are more) column in which it finds the given term

Option Explicit
Sub Ingredients()
    Dim i As Integer, j As Integer
    Dim srcStr As String

    srcStr = Application.InputBox("Enter a searchterm")

    i = 2
    While (Cells(i, 1).Value <> "")
        j = 2
        While (Cells(1, j).Value <> "")
            If InStr(1, Cells(i, 1).Value, Cells(1, j).Value) Then Cells(i, j).Value = "YES"

            If Cells(1, j).Value = srcStr Then Columns(j).Select
            j = j + 1
        Wend
        i = i + 1
    Wend

End Sub

EDIT2:

Option Explicit
Sub Ingredients()
    Dim i As Integer, j As Integer
    Dim srcStr As String
    Dim itemFound As Boolean

    srcStr = Application.InputBox("Enter a searchterm")

    j = 2
    itemFound = False
    While (Cells(1, j).Value <> "")
        If LCase(Cells(1, j).Value) = LCase(srcStr) Then itemFound = True
        j = j + 1
    Wend
    If itemFound = False Then Cells(1, j).Value = srcStr

    i = 2
    While (Cells(i, 1).Value <> "")
        j = 2
        While (Cells(1, j).Value <> "")
            If InStr(1, Cells(i, 1).Value, Cells(1, j).Value) Then
                Cells(i, j).Value = "YES"
            End If
            j = j + 1
        Wend
        i = i + 1
    Wend
End Sub

edited Mar 03 '17 at 13:17

answered Mar 03 '17 at 12:46

tretom

559
1
7
17

can you help to add message box prompt to ask user for what he want to find ? – sapna Mar 03 '17 at 12:51
can you help to add messge box prompt to ask user for what he want to find ? – sapna Mar 03 '17 at 12:52
I have 'Details' column only User runs a Macro and message box appears to ask what he want to find then whatever user enters it is looked into Details column and a new column is inserted with YES Flag – sapna Mar 03 '17 at 12:55
thanks it solves some of the query but could you review my edited question – sapna Mar 03 '17 at 13:13
text find runs in case sensitive please make it non case sensitive – sapna Mar 03 '17 at 13:13
EDIT2 is case insensitive, but it doesn't sort the columns alphabetically. I leave it to you :o) – tretom Mar 03 '17 at 13:22

score 0 · Answer 4 · answered Mar 06 '17 at 05:54

    'you must break down your problem in to 2 parts
    '1. Find the word in the srting and store the name in the next column
    'for you must use VBA-built-in functoin InStr
    '2.Loop through next column, and use select-case logic to put the name in the corresponding column
    'for this you may use select-case statement or any other suitable loop and logic

Excel Find text in column and create a YES flag in new column

4 Answers4