Solved Find multiple words/values in Excel

November 26, 2010 at 05:56:11
Specs: Windows 7 Professional 64-bits, Core 2 Quad 6600 / 4GB
Hello,

What I would like to do is to use the for example the Ctrl-F function in Excel to look for different words/values at once. Is this possible?

Even better would be a macro that searches a certain worksheet for lets say 10-100 predefined words/values. If the word/value is found, the row in which the word/value appears should be copied to a second worksheet.

Thanks in advance,

E.


See More: Find multiple words/values in Excel

Report •

✔ Best Answer
December 2, 2010 at 04:46:55
I suggest that you try the code in a backup copy of your workbook since macros can not be undone.

1 - Press Alt-F11 to open the VBA Editor
2 - Click on the name of your workbook in the left hand column
3 - Click on Insert...Module
4 - Paste the code into the right hand pane that opens
5 - You should see Blue, Green and Black text. If you see any Red, something's not right. Post back here describing what's Red.
6 - To Run the code, click anywhere in the code to place the cursor in the code, then click the Green arrow in the tool bar.

The code will either do what it is supposed to do or throw up an error. Post back with info on the error if that happens. How long it takes to run will depend how on much data it has to search through. When the cursor starts blinking in the code, it's done.

You can also Single Step through the code by pressing F8. Each time you press F8, one line of code will be executed. This can help narrow down problems if they occur. You can single step for awhile and then click the Run arrow once you're satisfied that the code is doing what you want.

In single step mode, you can switch back and forth between the workbook and the VBA Editor to see if lines are being copied. You can even size the VBA window so that you can see your workbook behind it to see if lines are being copied as expected.

You can assign the macro to a button, a function key or a menu item to make it easier to run once you are sure it is working. Google something like assign macro to button for various suggestions on automating that. You'll learn more by searching for a method that you like than if I just tell you how to do it.

Posting Tip: Before posting Data or VBA Code, read this How-To.



#1
November 26, 2010 at 08:37:34
Ctrl-F won't do you what you want.

A macro should be able to do it for you. Looping through a list of words/values using the .Find method is one possible way.

Before we could offer anything meaningful we would need to know some more details such as some examples of what we would be searching for and where we would be searching.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 28, 2010 at 23:39:06
Hello,

First of all thanks for helping! Below you will find a description of the the excel file. If it's necessary to really post part of the data, just tell me...

One column of my first Excel worksheet contains gene names (for example: Baf21) and all the other columns contain more information about that certain gene. So each row is specific for one gene. The first row in the worksheet contains the headers and does not have to be searched.

The purpose is to search for a set of genes in the "gene column" (as decribed above). The number of genes in the search set can vary between 1-500. If a match is found, it would be great to let excel copy the row in which the gene occurs (so with all the additional gene information) to for example the second worksheet. If a second gene is found the row should be copied to the second row in the second worksheet etc.

The first worksheet can contain up to 30000 rows (so 30000 genes with their info). I think I would never have more than 10 columns with additional gene information.

At this moment the excel sheet is built up like below:

column 1: header = Transcripts Cluster Id --> column with id numbers
column 2: header = Fold change([Ctrl64h] vs [Ctrl4h]) --> column which contains a number
column 3: header = Regulation ([Ctrl64h] vs [Ctrl4h]) --> column with UP or DOWN
column 4: header = genesymbol ---> gene column (this column needs to be searched)
column 5: header = refseq --> column with id's consisting of both numbers and letters
column 6: header = genbank --> column with id's consisting of both numbers and letters
column 7: header = ensembltranscript ---> column with id's consisting of both numbers and letters

Kind regards,

E.


Report •

#3
November 29, 2010 at 03:57:01
re: "The purpose is to search for a set of genes in the "gene column" (as decribed above). The number of genes in the search set can vary between 1-500."

You've done a fine job of describing the layout of your spreadsheet, but I don't see anything other than the 2 lines above related to the actual search.

What constitutes a "set of genes"? How are the members of that set determined so that we know what to search for?


For example, if I had the following list, how would we know which "set" to search for?

Baf21
Baf22
Baf23
Baf24
Baf25

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
November 29, 2010 at 07:36:51
The search set is variable. Probably this complicates things?

Sometimes the search set consists of 5 genes (like you mention for example Baf21 to Baf25). In other experiments I need to search for 30 or more different genes in the worksheet.

Isn't it possible to each time adjust the code before I make my search?

Workheet 1 -> View code -> adjust the code to that situation -> save -> search?

Kind regards,

E.


Report •

#5
November 30, 2010 at 00:46:43
Below one of my search lists

1110006O17Rik
1110067D22Rik
2310022B05Rik
2410008K03Rik
2810417H13Rik
4833442J19Rik
Aard
Acta2
Actg2
Adamts12
Adamts5
Ankrd55
Anln
Anxa3
Atoh8
Avpr1a
C1qtnf2
C330027C09Rik
Ccna2
Ccnb1
Cdh3
Chaf1b
Col5a2
Col8a1
Crip2
D14Ertd449e
Dag1
Dbi
Depdc2
Des
Diap3
Dmpk
Ebp
Ech1
Ect2
EG432466
EG628004
Ehd2
Emp3
Eno3
Epha3
Fdps
Figf
Gdf6
Gpr176
Gucy1a2
Gulp1
Gyg
Hand2
Hells
Hist2h3b
Hs6st2
Idi1
Igfbp3
Klhl13
Lbh
Lims2
Lmcd1
LOC638798
Lox
Loxl3
Mad2l1
Magt1
Mcm5
Mcm6
Mtss1
Myh11
Myl9
Ncapg2
Neto1
Neu3
Nid2
Nuak1
Nuf2
Pappa
Peg10
Pigk
Pir
Plekha2
Plp2
Plscr2
Pmp22
Popdc2
Prc1
Rab30
Rab7l1
Racgap1
Rapgef5
Rasl11b
Rfc4
Rnpep
Rrm1
Rrm2
Rtn1
Samhd1
Sdc2
Sepx1
Serpinb9b
Siae
Slc1a6
Smarcd3
Spon2
Taf9b
Top2a
Tpx2
Trpv2
Tspan12
Uhrf1
Wisp2
Zbtb8

Kind regards,

E.


Report •

#6
November 30, 2010 at 07:46:38
Will you be able to place the list of search items in a spreadsheet?

If so, the code could simply run down that list, searching for each item, and copying the row to wherever you need it copied to.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#7
November 30, 2010 at 10:36:28
Assumptions:

1 - Sheet 1 Row 1 contains Column Headings.
2 - Sheet 2 is the destination sheet for the copied rows.
3 - You want the same Column Headings on Sheet 2 that are on Sheet 1.
4 - Sheet 3 Column A contains the list of Gene names to search for, starting in A2.

Before running the following code, place the list a Gene Names to be searched for in Sheet 3 Column A, starting in A2.

I suggest running the code in a backup copy of your workbook since macros can not be undone.

Option Explicit
Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
 Sheets(2).Cells.ClearContents
 Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column D, copy it top the next row in Sheet2
  With Sheets(1).Columns("D")
    For gName = 2 To srchLen
      Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
        If Not g Is Nothing Then
          nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1
          g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
        End If
    Next
  End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
December 1, 2010 at 07:45:06
Hi. Thanks for the code. But can you modify it so that you can search in a cell that contains more than 1 word? Using the same logic and result but searching through a sentence and not assuming there is only 1 word in sheet 1 column D.

Thanks in advance


Report •

#9
December 1, 2010 at 09:26:35
Before I modify the code, it would help if I knew a little more about what is in Column D. There are multiple ways to search for part of text string and one way might work better than the other.

P.S. I don't need 110 examples of what is in Column D, just enough examples so that they cover the various possibilities - 1 word, 2 words, words in the middle, words at the end, etc.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#10
December 1, 2010 at 12:15:44
ok - thanks for looking. i dont know how to upload the excel sheet, but will try to explain.

if you could also widen the search so that it also looks in 2 additional columns as well, that would be ideal (e.g. Column B and I)

so, the data in sheet 3 consists of the following type of data:

SW44PCCPNIK01
SW44PCCPPAPP01
SW44PCCPPAPP02
SW44PCCPPAPU01
SW44PCCPSQLC01
SW44PCCPSQLD01
SW44PCCPSQLDV01
SW44PCCPSQLN01

these are server names, and have varying lengths. there are 240 names to loop through.

the data in the cells (B, D and I) consist of the following type of info (from an incident management tool)

Column B Cell : overnight batch job PCYDMGEXPD ended not OK

Column D cell: Alert - PCYDMGEXPD SOC request it gets logged to Hyperion (LFINSYS) SOC reference: HD383829 Alert tells soc to log as a P2 Further details to be sent by email

Column I cell: asked RPC Service Desk to force the job OK so as to cancel it

not all columns/cells will necessarily contain the name of the server but i only require the row to be copied over just the once - after a match has been found.
hope this makes sense?

happy to email you the spreadsheet so you can have a play.

thanks


Report •

#11
December 1, 2010 at 13:11:14
I think I missed something...you are not the original poster of this thread, are you?

I did not not notice that when you posted Response # 8.

Since your issue seems to be different than the OP's need to search for Gene names in a specific column, you should post your request in a separate thread.

If you want to reference this thread by copying the URL to your new thread, feel free.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#12
December 1, 2010 at 13:28:37
Thanks. I have created a post here if you would still like to help.


http://www.computing.net/answers/of...


Report •

#13
December 1, 2010 at 23:00:44
@DerbyDad03: Thanks for the help!

I will try the code as soon as possible.

Kind regards,

E.


Report •

#14
December 2, 2010 at 01:29:52
Probabily a stupid question, but how do I execute this macro?

I'm now as far as opening Excel, putting everything in the right worksheet, copying your code and then...?

Kind regards,

E.


Report •

#15
December 2, 2010 at 04:46:55
✔ Best Answer
I suggest that you try the code in a backup copy of your workbook since macros can not be undone.

1 - Press Alt-F11 to open the VBA Editor
2 - Click on the name of your workbook in the left hand column
3 - Click on Insert...Module
4 - Paste the code into the right hand pane that opens
5 - You should see Blue, Green and Black text. If you see any Red, something's not right. Post back here describing what's Red.
6 - To Run the code, click anywhere in the code to place the cursor in the code, then click the Green arrow in the tool bar.

The code will either do what it is supposed to do or throw up an error. Post back with info on the error if that happens. How long it takes to run will depend how on much data it has to search through. When the cursor starts blinking in the code, it's done.

You can also Single Step through the code by pressing F8. Each time you press F8, one line of code will be executed. This can help narrow down problems if they occur. You can single step for awhile and then click the Run arrow once you're satisfied that the code is doing what you want.

In single step mode, you can switch back and forth between the workbook and the VBA Editor to see if lines are being copied. You can even size the VBA window so that you can see your workbook behind it to see if lines are being copied as expected.

You can assign the macro to a button, a function key or a menu item to make it easier to run once you are sure it is working. Google something like assign macro to button for various suggestions on automating that. You'll learn more by searching for a method that you like than if I just tell you how to do it.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#16
December 7, 2010 at 06:47:11
Everything works great :-)

Thank you again for the help!

Kind regards,

E.


Report •

#17
July 11, 2011 at 11:49:59
DerbyDad03 This is a great discussion but i need some help tweaking this to make it work for what i am doing.

I have a list of Orders, about 45 of them, that i need to find in a master list of about 300 orders. I have to do this for multiple documents so i figured a macro just like the one above would be my best bet. I have copied the formula into the Macro field and it works but my only issue is that in sheet 1 it has multiple entries with the same Order Number. So your formula you gave us will only select the 1st one and then skip the other 3 or 4 of them that have the same order number and then go to the next Order Number and display it on sheet2. What do i need to change so it spits out not only the 1st order number but all of the data with the associated with the other ones with the same order number?

I cant seem to figure it out and you seem like the man with all of the answers so if you could help that would be great.


Report •

#18
July 12, 2011 at 09:08:28
This is what i have and i want to expand it to include all values found in Sheet1, column "B" not just the first one it finds but all of them?

Option Explicit
Sub OrderFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column B, copy it too the next row in Sheet2
With Sheets(1).Columns("B")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Sub

This is an example of my data I am looking for:

68006142
68006859
68006861
68006863
68006864

The information i need to search is in column B on Sheet 1

Thanks!


Report •

Ask Question