Extracting Records From Excel Database

[thorvision]

I have a 1000 row/15 column Excel database. One of the fields i
"Project Description" where it decribes the nature of a capita
project. I'm looking to find all records relating to "Fire an
Security" which could include words such as "safety", "sprinkler"
"Upgrade" "Fire" all within the Project Description field. I need t
be able to pull all those records. How do I perhaps use Excel'
advance filter feature to perform this search for what I may refer t
as key words. Or, is there a worksheet function I can use to do th
same. Thanks.

(e-mail address removed)

 

[Norman Jones]

Hi K,

I would suggest that you try the Advanced Filter option.

Debra Dalgleish has a good tutorial on using complex criteria at:

http://www.contextures.com/xladvfilter02.html

 

[Max]

Another option to play with, using non-array formulas ..

Sample construct at:
http://cjoint.com/?lEktDY0nSB
Extract_Records_KeyWords_in_Column_thorvision_misc.xls

In sheet: A
-----------
Keywords are listed in col A, in A1 down (till A100, say)

In sheet: B
-----------
Assume the table is in cols A to O, data from row2 down
The key col, "Project Description" is assumed in col C, from C2 down

Use an empty column to the right of the table, say col Q

Put in Q2:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A!$A$1:$A$100,C2))*(A!$A$1:$A$100<>""))=1,R
OW(),"")

Copy K2 down to say, K1000, to cover the max expected data
(Leave K1 empty)

In sheet: C
----------
Copy > paste the same col headers over from sheet B

Put in A2:
=IF(ISERROR(SMALL(B!$Q:$Q,ROWS($A$1:A1))),"",INDEX(B!A:A,MATCH(SMALL(B!$Q:$Q
,ROWS($A$1:A1)),B!$Q:$Q,0)))

Copy A2 across to I2, fill down to I1000
(cover the same range size as done in col Q in sheet B)

 

[Max]

Typo, line:

Copy A2 across to I2, fill down to I1000

should read:

 

[thorvision]

very grateful for your help

 

[Max]

You're welcome !