Filtering a List

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HomeBuyingHQ - 24 Sep 2007 04:40 GMT

I download real estate listings for sale into an Excel spreadsheet. I only
want for sale by owner listings and do not want listings with a realtor. I
have a list of words (Century 21, Prudential, agent, realtor, etc.) that I
want to filter the list with so I don't email to those people.

Price Email Description
1. $300K John@msn.com Nice house. Listed with Century 21.
2. $350K Paul@msn.com Great house. Call Paul.
3. $320K Mark@msn.com Beautiful house, email Mark, realtor.

For instance, in this list, I am only interested in number 2. Number 1
contains the word "Century 21" and number 3 contains the word "realtor". How
can I sort or fiter the list so it only contains number 2?

Reply to this Message

Max - 24 Sep 2007 07:00 GMT

One way using autofilter on a helper col which should deliver it here

Assume source table as posted in cols A to D,
data from row2 down, where col D = Description

List the exclusion strings in F1:F4, eg:

Century 21
realtor
Prudential
agent

Put in E2:
=IF(D2="","",SUMPRODUCT((ISNUMBER(SEARCH($F$1:$F$4,D2))*($F$1:$F$4<>""))))
Copy E2 down to the last row of data in col D (Description). Col E will
return zeros where the data in col D doesn't contain any of the exclusion
strings listed in F1:F4. Now just apply autofilter on col E, filter out zero.
The filtered list will be the results that you seek. Adapt to suit.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I download real estate listings for sale into an Excel spreadsheet. I only
> want for sale by owner listings and do not want listings with a realtor. I
[quoted text clipped - 9 lines]
> contains the word "Century 21" and number 3 contains the word "realtor". How
> can I sort or fiter the list so it only contains number 2?

Reply to this Message

Max - 24 Sep 2007 09:08 GMT

Just to add that should you want the expression's search for the exclusion
strings to be stricter, case sensitive, you could replace SEARCH with FIND.
FIND is case sensitive. SEARCH is not.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message