Search excel spreadsheet for words

Microsoft Excel 2004 for mac
October 5, 2012 at 14:10:26
Specs: Windows XP
Hello Could some one help, we have 50,000 row spreadsheet with parts # and descriptions. I would like to search descriptions by 4 words, they are in different orders in different parts. Filters help to some degree, but macro that would search for words in any order (in single column) would be great improvement.

See More: Search excel spreadsheet for words

Report •


#1
October 5, 2012 at 18:59:34
We are going to need an example of your data before we can offer any code. We are also going to need a better description of your search criteria and how the macro will know what to search for.

Before posting your data, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 5, 2012 at 19:50:56
Try this formula, it will search your String to Search
and try to find all four words from your Keyword list
and will report TRUE if all four words are found.

If your Data looks like:

        A                                B          C      D
1) Strings to Search             Contains keyword?     Keywords
2) The Quick Brown Fox Jumps           TRUE               the
3) The Quick Brown Fox Leaped          FALSE            quick
4) The Quick Red Fox Jumps             TRUE               fox
5) The Quik Red Fox Sleeps             FALSE            jumps

In Cell B2 enter the formula:
=IF(NOT(ISERR(AND(SEARCH($D$2,A2),SEARCH($D$3,A2),SEARCH($D$4,A2),SEARCH($D$5,A2)))),TRUE,FALSE)

Drag the formula down three cells.

You can also change the Keywords and use a different sequence of words in your
Keyword list.

You can also use it in Conditional Formatting,
and change the color of those strings that contain
all four key words.

It's a bit long, but it works.

MIKE

http://www.skeptic.com/


Report •

#3
October 5, 2012 at 20:29:09
If Mike's formula works for you, you can make it a bit shorter if you use an Array Formula:

=IF(NOT(ISERR(AND(SEARCH($D$2:$D$5,A2)))),TRUE,FALSE)

Use Ctrl-Shift-Enter to enter the formula and produce the brackets, then drag it down.

{=IF(NOT(ISERR(AND(SEARCH($D$2:$D$5,A2)))),TRUE,FALSE)}

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 6, 2012 at 05:01:42
DerbyDad03,

Tried your array formula and have added it to my bag of tricks.

I started out using SUMPRODUCT() & SEARCH()
dropped the SUMPRODUCT() but never thought to make it an array.

Thanks.

MIKE

http://www.skeptic.com/


Report •

#5
October 6, 2012 at 13:51:32
Hi, Thanks.
here is sample data
M# Description
12345 MAN Oring 123-02-333 Cyl bottom oil
123456 MAN Oring 123-02-335 Cyl top water
234567 MAN Oring 123-03-335 Head blac
345678 MAN Oring 123-03-027 Head green
456789 MAN Oring 123-04-012 Inj bottom
567890 MAN Oring 123-04-013 Inj midle
345679 MAN Oring 123-04-014 Inj top
456790 green hose 3/4” garden
567891 garden hose 50'
345680 garden hose fitting male barb
345679 garden hose fitting female barb
456790 swivel fitting male garden hose
567891 swivel fitting female garden hose
345680 garden hose adapter fitting male
123469 garden hose adapter fitting female
The best solution would display something like filters only the rows that contain the selected words or list like search.
Filters works to some degree but the "contain" is berried deep and there only search for two words available. The search option should have "contain" only or as default search options.
Our descriptions have words in different order even for the same type of items.
Tagging lines with TRUE or with color would steel require scrolling 50,000 lines to find the right one from multiple selections. Search by two words frequently produce 100 lines or more.

Report •

#6
October 6, 2012 at 17:11:08
The best solution would display something like filters only the rows that contain the selected words or list like search.

So just filter on the TRUE or FALSE.

MIKE

http://www.skeptic.com/


Report •


Ask Question