Extracting Records From Excel Database
 

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




--
thorvision

chess
------------------------------------------------------------------------
thorvision's Profile: http://www.excelforum.com/member.php...fo&userid=1778
View this thread: http://www.excelforum.com/showthread...hreadid=489346

Extracting Records From Excel Database
 

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

---
Regards,
Norman



"thorvision" wrote
in message ...

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




--
thorvision

chess
------------------------------------------------------------------------
thorvision's Profile:
http://www.excelforum.com/member.php...fo&userid=1778
View this thread: http://www.excelforum.com/showthread...hreadid=489346

Extracting Records From Excel Database
 

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"thorvision" wrote
in message ...

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




--
thorvision

chess
------------------------------------------------------------------------
thorvision's Profile:
http://www.excelforum.com/member.php...fo&userid=1778
View this thread: http://www.excelforum.com/showthread...hreadid=489346

Extracting Records From Excel Database
 

Typo, line:
Copy A2 across to I2, fill down to I1000

should read:
Copy A2 across to O2, fill down to O1000

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Extracting Records From Excel Database
 

very grateful for your help


--
thorvision

chess
------------------------------------------------------------------------
thorvision's Profile: http://www.excelforum.com/member.php...fo&userid=1778
View this thread: http://www.excelforum.com/showthread...hreadid=489346

Extracting Records From Excel Database
 

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"thorvision" wrote
in message ...

very grateful for your help