Find rows that contain one of 6 variations of specific character string and paste those rows into another worksheet

Posted on 2014-09-15
Last Modified: 2014-09-15
Sheet 1 contains 54,000 rows of text and/or numbers. I am looking for a macro that will find those rows that contain any of the variations of the 6 RCO strings outlined below and copy those rows that meet this criteria into Sheet 2. The RCO string could be located anywhere within the row.

Variations of RCO string:
"- RCO"
"– RCO"
"— RCO"

Question by:Andreamary
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
ID: 40323695
You can achieve this with a user-defined function (UDF) like so:
Function Find_Matches(Test_Cell As Range, Lookup_array As Range) As String
    Dim cl As Object
    For Each cl In Lookup_array
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            Find_Matches = Find_Matches & cl.Value & ", "
        End If
    Next cl
    If Find_Matches <> "" Then
        Find_Matches = Left(Find_Matches, Len(Find_Matches) - 2)
        Find_Matches = "NOT FOUND"
    End If
End Function

Open in new window

In Excel, you enter this function this way:

For example, if your cell to check is in A2 and you have a list of values to check is on Sheet3 in cells A2:A9 then you'd enter:

The function will show ALL possible matches in a string, otherwise will return "NOT FOUND" (that result can be changed in the UDF, of course).

You can copy this down, filter out the "NOT FOUND" values, and then copy/paste them to Sheet2.

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40323781
...and here is a macro that will copy all values from Sheet1, Column A that have any of the six strings listed into Sheet2 (starting in row 2):
Option Explicit
Sub Copy_RCO()
    Dim rng As Range
    Dim cl As Object
    Dim x As Integer
    Dim r As Long
    Dim arrRCO() As Variant
    arrRCO() = Array("-RCO", "- RCO", "– RCO", "–RCO", "—RCO", "— RCO")

    r = 2
    Set rng = Range("A2:A" & Cells.SpecialCells(xlLastCell).Row)
    For Each cl In rng
        For x = 0 To UBound(arrRCO())
            If InStr(1, cl.Value, arrRCO(x), vbTextCompare) > 0 Then
                Sheets("Sheet2").Cells(r, 1).Value = cl.Value
                r = r + 1
            End If
        Next x
    Next cl
End Sub

Open in new window


Author Comment

ID: 40323933
Hi Glenn,

Thanks very much for both the UDF and the macro. I had success with the UDF, but the macro is giving me a run-time error '13', Type mismatch, on line 16:

 If InStr(1, cl.Value, arrRCO(x), vbTextCompare) > 0 Then

It would be nice to have the macro option if possible.


Author Closing Comment

ID: 40323947
Hi Glenn,

On further investigation, I found I had a few rows that contained "#NAME?" that were causing the problem and preventing the macro from being able to run. Once I deleted those offending rows, the macro worked perfectly.

Thanks so much for the quick and thorough solutions!

LVL 27

Expert Comment

by:Glenn Ray
ID: 40324031
Glad I was able to help.


Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 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