Searching text entries

[MikeLondon]

I am a photographer and I have pictures in an on-line library.

I need to upgrade the keywords on my pictures and the library has sent me an excel spreadsheet with all the data they currently hold on my pictures.

There are 17 columns in the spreadsheet and 7,900 rows, one for each picture I have on the site.

I want to extract groups of file records, for example all the 'Brazil' pictures or all the 'education' pictures. Once I have all the Brazil pictures gathered I can add group keywords to these pictures.

Because the pictures were scanned and uploaded to the site by a third party the image file names are not a useful way of finding the relevant records.

Of the 17 columns on the spreadsheet there are three which contain words I could search for to find all the 'Brazil' pictures: one called caption, one called keywords and one called description.

Each cell in these fields can each contain quite large records, for instance there may be 20 words in one keyword cell for a particular picture or the description cell for that picture may contain 60 words. I need to search in these cells for the word Brazil and then be able to gather all the rows that have Brazil somewhere in their entry.

I am a bit of an expert in photoshop but a basic beginner in excel.

I have tried the search, filter and autofilter functions in excel but these will not search within cells for particular words.

Does anyone have any ideas about how I can do this?

I am on a mac using os 10.4.11 and excel 10.1.0

many thanks,

Mike

[lucas]

Mike, I have moved this to the Excel help forum. I think you will find help here much quicker than in the misc. help forum.

[lucas]

Mike,
Just a quick look at this. The code below will look in column D for any instance of the string labor and will highlight all rows with that string in column D.......not sure if that is what you are looking for. You could easily remove the highlighting when done using code(method shown in example workbook).

the code will search the entire sheet down to the last row that contains data in column A.

All of the settings can be changed in the code if it looks like it will help in your project.
[VBA]Option Explicit
Option Compare Text
Public Sub DeleteRowsWithLabor()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
'change next line to determine which column to find last row in.
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
' iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, TEST_COLUMN).Value Like "*Labor*" Then
' Rows(i).Delete
Rows(i).Interior.ColorIndex = 6
End If
Next i
End With
End Sub[/VBA]

[Simon Lloyd]

Or maybe something like this, it assumes your data to search through is on sheet1 and that you have a sheet2 where the found data can be sent to!, enter your search word in the input box.
[VBA]
Sub Find_n_Show_Range()
Dim rngFind As Range
Dim FoundRng As Range, MyCell As Range
Dim rngLook As Range
Dim ib As String
ib = InputBox("?")
Set rngLook = Sheets("Sheet1").Range("A1:Q7100")
Set FoundRng = Sheets("Sheet2").Range("A65536").End(xlUp)
For Each MyCell In rngLook

Set rngFind = rngLook.Find(What:=ib, After:=MyCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If MyCell = "" Then Exit Sub
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).Value = ib
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(0, 1).Value = rngFind.Address & " In row " & rngFind.Row
Set rngFind = rngLook.FindNext(rngFind)
Next
End Sub

[/VBA]

[mikerickson]

You can use AutoFilter >CustomFilter to show those rows whose cells contains "Brazil"

[MikeLondon]

Thanks Lucas and Simon for your codes. It makes me realize how little I know about excel!

Mike Rickson's suggestion about using the autofilter is the easiest for a novice like me. I have tried this but I can only get the autofilter - custom filter to search on one column. The word Brazil could be in any of the three columns and I need to pick up all rows with the word Brazil.

Is there any way for autofilter - custom filter to do a search on more than one column and show all the rows that match the selected criteria - 'Brazil' or 'education' etc?

Best,

Mike

[Simon Lloyd]

Mike if using code scares you there is an easier way, go to Edit, Find in the box Find What type Brazil then click Find All now you get a list below the find box which is clickable i.e you can click an entry here and it will select that cell!

[mikerickson]

Advanced filter will allow you to search multiple columns.
The Attached shows an example. Note the pattern matching used in the criterial range ("*Brazil*").

[lucas]

That's very nice Mike.....new trick for me. Thanks.

[mikerickson]

Advanced Filter's got a learning curve, but, dang, its powerful.

[MikeLondon]

Hi All,

Thanks for your posts. Almost there! Simon, your solution would be wonderful, but when I go to 'find' on my version of excel and type Brazil into the 'find what' box there is no 'find all' option available. The only options I have are find next, close or replace. How can I access the 'find all' button? Is there an excel upgrade I need to do?

Mike, thanks for your advanced filter example which I downloaded and it works well on your sheet. But when I tried your method on my sheet I could not see how to enter the the search term 'Brazil' or 'education' into the advanced filter dialogue box as the only options available are the list range and criteria range.

On the spreadsheet I need to sort the data is organized in the following way:

Each photo record has its own row. There are 7900 rows on the sheet. Column A is the photo's file number. Column B is the 'caption' field, column C is the 'essential keyword' field, Column D is the 'main keywords' field, column E is the 'comprehensive keyword' field and column F is the 'description' field. There can be more than 20 words in any one of these cells or fields. I need to search through all of these to find all the 'Brazil" pictures or all the 'education' pictures.

So far we are almost there but not quite.

If you can help me do this I would really apreciate it.

Mike

[mikerickson]

the search term is not entered into the Advanced Filter dialog box, its entered into the Criteria range. You could set up the sheet with a fixed criteria range and then write a macro that invokes an input box to fill the criteria range and then perform an Advanced filter.

Or since, its a self use spreadsheet, do it manually.

[mdmackillop]

This will search columns 1, 2 and 3 (adjust to suit) add the Search term into a helper column (T in this case) then filter on the result.
[VBA]
Option Explicit
Option Compare Text
Sub FindData()
Dim Rng As Range
Dim ToFind As String
Dim c As Range, FirstAddress As String
ToFind = InputBox("Enter search string")
Set Rng = Union(Columns(1), Columns(2), Columns(3))
With Rng
Set c = .Find(ToFind, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Cells(c.Row, 20) = ToFind
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Columns(20).AutoFilter Field:=1, Criteria1:=ToFind
End Sub

[/VBA]

[lucas]

I learned something from Mike's manual filter on mult columns but Malcolm has offered another simple and elegant solution.....this has been a pretty good thread...