text string seach and count???

[Matthew Recknagel]

Alright i have a term i want to search say "Large Dog"
I have a list of about 200 rows of files (located in a few main folders on my computer) that i want to search through each of the text strings.
Is there a way that i can search through the entire list and have the formula return return a count of how many files have "Large Dog" in them

Optional: I know for a fact that the files that have persay "Large Dog" in them all reside in the same folder, is there a way to return the count of how many files there are with a hyperlink to that folder?

Thanks so much in advance, will respond if any additional details are needed!
Matt

 

[pbornemeier]

Welcome to the Board!
Many questions arise:
1. What do you mean by "I have a list of about 200 rows of files"?
1a. Where is that list?
1b. What directories contain the files?
1c. Which files are in which directories?
1d. Are files duplicated between directories?
2. What kind of files are they?
2a. If they are Excel workbooks, which worksheets contain the data to be searched?
2a1. How is that data orgainzed in those worksheets?
3. Is the search case sensitive?
More questions will probably follow your answers.

 

[Matthew Recknagel]

1. I have used a file generating list, that searches through selected folders and lists out their files name, location, date modified, type etc... it has generated a large list that will be continuously changing. here is a look of what i mean (red text will be subject to change as they could move the folders to different drives on their computer, blue text will always remain the same)

<TABLE style="WIDTH: 777pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1036><COLGROUP><COL style="WIDTH: 777pt; mso-width-source: userset; mso-width-alt: 37888" width=1036><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 777pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=21 width=1036>O:\department\Engineering\MALAYSIA - DRAWINGS & PROGRAMS\101316929\3.0 Document Control</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 777pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1036><COLGROUP><COL style="WIDTH: 777pt; mso-width-source: userset; mso-width-alt: 37888" width=1036><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 777pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=1036>O:\department\Engineering\MALAYSIA - DRAWINGS & PROGRAMS\101316929\5.0 Operational Routings</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 777pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1036><COLGROUP><COL style="WIDTH: 777pt; mso-width-source: userset; mso-width-alt: 37888" width=1036><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 777pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=1036>O:\department\Engineering\MALAYSIA - DRAWINGS & PROGRAMS\101465833-E (D00242752-D)\3.0 Document Control</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 777pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1036><COLGROUP><COL style="WIDTH: 777pt; mso-width-source: userset; mso-width-alt: 37888" width=1036><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 777pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=1036>O:\department\Engineering\MALAYSIA - DRAWINGS & PROGRAMS\101465833-E (D00242752-D)\7.0 Programming and Tooling\3D MODELS</TD></TR></TBODY></TABLE>
1a. this list is located in an excel file which headers such as
ParentFolder; DateCreated; DateLastAccessed; DateLastModified; FileName; Type

1b. Directories will be subject to change

1c. In this case the part ID folders are located in MALAYSIA - DRAWINGS & PROGRAMS, but as the in the future they may be located in different areas (the list generating program takes care of this issue)

1d. Yes there could be duplicate files between the folders (Ex. Test01 template file could be in each of the folders.

2. The file type varies, PDF, word, notepad, excel, .tiff, .dwg... but this can also be listed out

2a. The only excel workbook i am concerned in search is the one that contains the list above.

2a1. Shown above

3. The search is not case sensivite to me, but if the equation we use is, then that wont be a problem


Here is what i am trying to do,
<TABLE style="WIDTH: 516pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=688><COLGROUP><COL style="WIDTH: 219pt; mso-width-source: userset; mso-width-alt: 10678" width=292><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9472" width=259><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 219pt; HEIGHT: 55.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl250 height=74 rowSpan=4 width=292>Action Step/ Kaizen Events</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl253 rowSpan=4 width=62>Owner</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl248 rowSpan=4 width=75>Assisting</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 194pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl245 width=259>Top Level #</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 194pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl245 height=21 width=259>101316929</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 194pt; HEIGHT: 26.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl246 height=35 rowSpan=2 width=259>File Check</TD></TR><TR style="HEIGHT: 13.5pt" height=18></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl233 height=17>1.0 Detail out the project Plan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl238></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl234 height=17>2.0 Validate the plan with the team</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl239></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 219pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl240 height=17 width=292>3.0 Document Control</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl241 width=75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl239></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 219pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl235 height=17 width=292>3.1 Data Sheets</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237>Ron</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl241 width=75></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl244></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 219pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl235 height=17 width=292>3.2 Bill of Materials</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl237>Sally</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl241 width=75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl239></TD></TR></TBODY></TABLE>

I would like in the "File Check" column to have an equation that searches for the number "101316929" and also "3.0 Document Control", then be able to drag it down so the next row searchs for "101316929" & "3.1 Data Sheets" etc. it would have to search the list I pasted above.

If at all possible return with a count of how many have "101316929" & "3.0 Document Control" and possible (but not necessary) a hyperlink linking to that folder (I do have the folder path generated so all it would be is a cell reference)

Any help is appreciated!
Thanks,
Matt

 

[pbornemeier]

I cannot think of a simple way to search all of those file types using Excel. If the files to be searched were all Excel, Word or text files, then this could be done in a reasonably simple fashion. Since they are not, I will recommend one (or both) of the options below before leaving this thread for someone with more knowlege in the search area.

1) Use the search function in windows explorer (F3). This allows you to specify a word or phrase to search for. If you use *.* for the file name then file name will not limit the search. The program described here: http://www.nirsoft.net/utils/sysexp.html allows you to save the results of the search.

2) Install Google Desktop. It searches and indexes document content for many file types and allows you to query your computer as you would query the web.

Good luck & I regret I could not be of more assistance.

 

[Matthew Recknagel]

I am not sure what the file type has to do with the equation i am looking for? All i have is a list in excel that needs to be searched through text string search (possibly a combined search i.e. 2 different terms) and return a count/hyperlink

 

[pbornemeier]

I misunderstood. I thought you were trying to search the contents of the files on your list.

For the output of your search program in the Excel file, is this the column layout?
A ParentFolder
B DateCreated
C DateLastAccessed
D DateLastModified
E FileName
F Type

Does the output format you specified in post occupy columns A:D?

 

[Matthew Recknagel]

Yes that is correct

 

[pbornemeier]

Create a dynamic named range on your file list worksheet named "rngFileName"

=OFFSET(Sheet2!$E$1,1,0,COUNTA(Sheet2!$E:$E)-1,1)

This will define the area that will be searched. Formula assumes the filenames are in column E, there is a header in that column there are no blank rows in your list of files and that there is nothing in the cells below the last filename.

In your output, I am assuming that the header consists of 3 merged rows and occupies columns A:D
This formula would go in cell D7 (the 3.0 Document Control File Check cell)

=SUMPRODUCT(--(ISNUMBER(SEARCH(D$2,rngFileName))),--(ISNUMBER(SEARCH($A7,rngFileName))))

as it is dragged down it will the formula will adjust to look at the corresponding cell in column A.

When you check a different top level number you will have to change the formula value of D$2 to the appropriate value.

This is very dependent on the accuracy of the filenames in your list. If the filenames were not autogenerated by the originating program, I think there will be some percentage of files that are not accurately named and so the results for the formula will not display what you intend.

Please explain again the part of your request dealing with a hylerlink.

 

[Matthew Recknagel]

Ok I havent given your code a test quite yet but what I am imagining is that once it returns the sum of how many items it found from the search, a hyperlink would be generated linking to the "ParentFolder" in Column A.

 

[pbornemeier]

The previous code is searching column E. To change it to the parent folder column make this change to the name definition:



=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)
</PRE>