Help needed with creating a macro or function for finding specific keywords & then move to Sheet2

W

Wenduff

New Member
Joined
May 4, 2015
Messages
17
I have a dataset where I'm trying to pull out all line items noting my city & then moving those line items matching the city into a separate sheet. I created some fake data to see if anyone can help me out. The city in my fake data is San Diego. Any help on my question is very much appreciated!

I have a sheet of chat logs and am just looking to run a macro or function that searches for specific keywords (San Diego) in a couple of columns (an OR situation), and if any are found to move that entire row in which the keywords are found to a second sheet.

Example:

Search for "San Diego" in both Columns J and K. If found, copy the row in which it is found and move it to Sheet2. The actual data is hundreds of line items so looking for a way to do this without having to go line by line - big waste of time.

Any assistance is greatly appreciated.

Thank you!

RecordSenderSender NicknameReceiverReceiver NicknameMessage Date/Time - UTC+00:00 (M/d/yyyy)MessageMessage TranslatedMessage DirectionConversation IDChat TypeMessage StatusDurationAttachmentLocation Address
1​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:03:12 PMSendIncomingGroupReadText
2​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 5:03:19 PMokokIncomingGroupReadText
3​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 5:03:34 PM5555 Aero Drive, Suite 103, San Diego, CA 92108
NAME:Josh Miller		5555 Aero Drive, Suite 103, San Diego, CA 92108
NAME:Josh Miller		IncomingGroupReadText
4​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:04:32 PMSend another addressIncomingGroupReadText
5​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:04:35 PMFastIncomingGroupReadText
6​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:04:45 PMIncomingGroupReadText
7​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 5:04:52 PM1313 Palisades Dr, San Diego, CA 92110
NAME: Bob Marley		1313 Palisades Dr, San Diego, CA 92110
NAME:Bob Marley		IncomingGroupReadText
8​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:06:45 PMBro they want good addressIncomingGroupReadText
9​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 5:06:58 PMAll goodIncomingGroupReadText
10​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:07:08 PMAny other addressIncomingGroupReadText
11​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 5:07:16 PM1986 Mira Mesa Blvd, San Diego, CA 92126
NAME:Madison Duhamel		1986 Mira Mesa Blvd, San Diego, CA 92126
NAME:Madison Duhamel		IncomingGroupReadText
12​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 5:07:39 PM14498 Poway Rd, Poway, CA 92064
NAME:Mike Murphy		14498 Poway Rd, Poway, CA 92064
NAME:Mike Murphy		IncomingGroupReadText
13​
918555121212​
13809554247@g.usWestern Union6/13/2023 5:07:48 PMUsing itIncomingGroupReadText
14​
13236099111​
Bob8370123344630@g.usPackage6/13/2023 5:36:45 PM350 S Chadwick Ave, El Cajon, CA 92020
NAME:Kristen Bellflower		350 S Chadwick Ave, El Cajon, CA 92020
NAME:kristen BellflowerTRISTAN SIMPSON		IncomingGroupReadText
15​
918884630303​
8370123344630@g.usPackage6/13/2023 5:36:29 PMAddress for 15kIncomingGroupReadText
16​
13236099111​
Bob8370123344630@g.usPackage6/13/2023 5:48:35 PMokokIncomingGroupReadText
17​
13236099111​
Bob8370123344630@g.usPackage6/13/2023 5:48:50 PM899 Mira Mesa Blvd, San Diego, CA 92126
NAME:Will Samson		899 Mira Mesa Blvd, San Diego, CA 92126
NAME:Will Samson		IncomingGroupReadText
18​
918884630303​
8370123344630@g.usPackage6/13/2023 5:48:29 PM1 more addressIncomingGroupReadText
19​
13236099111​
Bob8370123344630@g.usPackage6/13/2023 7:43:46 PMokokIncomingGroupReadText
20​
918884630303​
8370123344630@g.usPackage6/13/2023 7:35:41 PMAmount 15000IncomingGroupReadText
21​
918884630303​
8370123344630@g.usPackage6/13/2023 7:40:35 PMBro 1 more addressIncomingGroupReadText
22​
13236099111​
Bob13809554247@g.usWestern Union6/13/2023 7:00:37 PMToo far awayIncomingGroupReadText
23​
918555121212​
13809554247@g.usWestern Union6/13/2023 7:00:29 PMZip codeIncomingGroupReadText
24​
918555121212​
13809554247@g.usWestern Union6/13/2023 7:00:38 PMNYIncomingGroupReadText
25​
918884630303​
8370123344630@g.usPackage6/13/2023 7:35:35 PM899 Mira Mesa Blvd, San Diego, CA 92126
NAME:Will Samson

Tracking - 1zy271890167967532

Sender- Bruce Hendersen		899 Mira Mesa Blvd, San Diego, CA 92126
NAME:Will Samson

Tracking - 1zy271890167967532

Sender- Bruce Hendersen		IncomingGroupReadText
26​
918555121212​
13809554247@g.usWestern Union6/13/2023 7:00:54 PMAny knowledge person can doIncomingGroupReadText
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Not sure if you want the header row copied or not, anyhow, here's a couple of options to play with using a copy of your workbook.
Using Advanced filter:
VBA Code: 
Option Explicit
Sub San_Diego_V1()
    Dim rCrit As Range, rCopyTo As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")      '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    Set rCopyTo = ws2.Range("A1")
    With ws1.Range("A1").CurrentRegion
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2, 1).Formula = "=OR(COUNTIF(J2,""*San Diego*"")>0,COUNTIF(K2,""*San Diego*"")>0)"
        .AdvancedFilter xlFilterCopy, rCrit, rCopyTo
    End With
    rCrit.Cells(1).Resize(2, 1).ClearContents
End Sub

Using arrays:
VBA Code: 
Option Explicit
Sub San_Diego_V2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")      '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    Dim a, b, i As Long, j As Long, k As Long
    a = ws1.Range("A2", ws1.Cells(Rows.Count, "O").End(xlUp))
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    
    j = 1
    For i = 1 To UBound(a, 1)
        If a(i, 10) Like "*San Diego*" Or a(i, 11) Like "*San Diego*" Then
            For k = 1 To UBound(a, 2)
                b(j, k) = a(i, k)
            Next k
            j = j + 1
        End If
    Next i
    ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel Function Help Needed, for transferring data between sheets with Macros or functions
Replies
1
Views
463
JKM00
J
G
  • Question Question
Help Using COUNTIFS Function
Replies
4
Views
713
Mick Peters
M
P
  • Question Question
Problem with running 2 different macros
Replies
5
Views
938
pupsia
P
D
  • Question Question
Need suggestions on ‘Find’ function variation
2
Replies
12
Views
1K
DLB20720
D
M
  • Question Question
I need help with Excel formula!!!
Replies
1
Views
497
venkat1926
V

Forum statistics

Threads
1,224,814
Messages
6,181,120
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top