Move cells containing keywords to another column

Column A has about 5K rows of domain names, Column B has about 20 keywords (each keyword in one row). How do I move the ones in A which contain keywords to Column C, and shift empty cells up in A?
Who is Participating?
rspahitzConnect With a Mentor Commented:
The attached code seems to work for me.  I simply added a button and linked it to that code.

Sub GetKeyDoms_Click()
    Dim iRowKey As Integer
    Dim iRowDom As Integer
    Dim iDomCount As Integer
    Dim iRowMatches As Integer
    Dim strKeyword As String
    Dim strDomain As String
    iDomCount = Selection.Row
    iRowMatches = 2 ' set the next row for a matched item
    iRowKey = 2 ' set the next row for a keyword to check
        ' get the next keyword
        strKeyword = Cells(iRowKey, 2).Value
        If strKeyword = "" Then
            Exit Do
        End If
        ' see if it matches any domains
        For iRowDom = iDomCount To 2 Step -1
            strDomain = Cells(iRowDom, 1).Value
            If strDomain = "" Then
                Exit Do
            End If
            If InStr(strDomain, strKeyword) > 0 Then
                ' keyword found, copy it
                Cells(iRowMatches, 3).Value = strDomain
                iRowMatches = iRowMatches + 1
                ' and delete the original
                Cells(iRowDom, 1).Delete Shift:=xlUp
                iDomCount = iDomCount - 1
            End If
        iRowKey = iRowKey + 1
End Sub

Open in new window

Well, depending on how easily you want this done, you could always use a VLOOKUP to locate a keyword and, based on that, copy it into C.  however, if your goal is to get a new version of column A, it seems that a macro might be the way to go.

so starting with the VLOOKUP idea, in cell C1 put this:

=VLOOKUP(B1, $A$1:$A$9999, 1, FALSE)

If the keyword exists, you should see the domain name otherwise you'll see an error.
Copy this formula down next to each keyword and you'll see the first domain name that contains that keyword.
If you want to show blank instead of the error, try this:

=If(ISERR(VLOOKUP(B1, $A$1:$A$9999, 1, FALSE)), "", VLOOKUP(B1, $A$1:$A$9999, 1, FALSE))

Is that a good start or am I off track for what you want?
mmcompactAuthor Commented:
both Column A and keywords list are different each time when I get a new file. VB code will be better than VLookup
See the sample file, I need to move the ones in A to C, the actual file has 5000 rows

Oh, and at the end, you'll probably want to add something like:


so the cursor ends up at a nice place instead of the original end of the domain list.
mmcompactAuthor Commented:
thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.