Filter Formula

[Josh O.]

I am trying to find a way to create a filtered list based on criteria. I
have the following formulas working in another spreadsheet...and I was
thinking maybe I could alter them to work.

Current Formulas:
Column A: Source Data (in this example Customer Name)

Column B:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Column C: =IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell D2: Filter Value

Basically the formula (from Max) filters the data in column B...giving me a
compact list of values that contain the value I type in cell D2.

Is it possible to add criteria to this formula? Or use another formula to
work? For example, I want a result list of values in Column C, from Column
A if E2=value in column B.

Column A: Source Data - Customer Name

Column B: Source Data - Variable Property

Column C: Similar Formula as above column B
=IF(ROW(A1)>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Column D: Similar Formula as above Column C
=IF(ISNUMBER(SEARCH($D$1,A1)),ROW(),"")

Cell E2: Filter Value

I don't know if that is clear or not...I would appreciate any help.

 

[Max]

This set-up should deliver what you're after ..

Col A = Cust names, from row2 down, with possible duplicates
Col B = Corresponding values to be returned for the particular customer,
whose name will be entered in E2 (E2 will house the filter value)

Put in C2:
=IF(ROW(A1)>COUNT(D),"",INDEX(B:B,SMALL(D,ROW(A1))))

Put in D2:
=IF($E$2="","",IF(ISNUMBER(SEARCH($E$2,A1)),ROW(A1),""))
(Leave D1 blank)

Select C22, copy down to cover the max expected extent of data in col A.
Col C will return the required results from col B for the cust name entered
in E2

 

[Josh O.]

Got it. Works perfect. Thanks Max.

Is it possible to use multiple criteria? Maybe by using another cell along
with E2 (as If E2 or E3)?

 

[Max]

Got it. Works perfect. Thanks Max.
Welcome.

Is it possible to use multiple criteria?
Maybe by using another cell along with E2 (as If E2 or E3)?

Yes, of course.

Assuming the range E2:E10 will house the input filter criteria

Just replace the formula in D2 with:
=IF(SUMPRODUCT((ISNUMBER(SEARCH($E$2:$E$10,A2))*($E$2:$E$10<>""))),ROW(),"")
(Leave D1 blank)

Then copy D2 down to cover the max expected extent of data in col A, as
before.
(Col C formulas unchanged). Col C will return all required results.

Adapt the input range to suit ..

 

[Josh O.]

I wish I understood how it works better...so I don't have to bug you with
questions.

Just one more and I am set:
How about searching for the filter values in 2 different columns?

 

[Max]

How about searching for the filter values in 2 different columns?

Assuming input filter values could be entered within E2:E10, and within
G2:G10, replace the formula in D2 with:

=IF(OR(SUMPRODUCT((ISNUMBER(SEARCH($G$2:$G$10,A2))*($G$2:$G$10<>""))),SUMPRODUCT((ISNUMBER(SEARCH($E$2:$E$10,A2))*($E$2:$E$10<>"")))),ROW(),"")

Copy down as before ..

(No change to col C's formulas)

 

[Josh O.]

Sorry. I wasn't clear on that one. I am getting all excited, because these
work so well.

By different columns I was referring to the original data. If I had the
Customer Name in column A, and city name in another column...then in the
Filter Values I could put part of the community name in e2 and a city name in
e3.

Example:
Column A: Customer Name
Column B: City
Column C: Formula (result)
Column D: Formula (ISNUMBER)
Column E: Filter Values (E2:E10) - It would ok if the "Customer" and "City"
Filter values were in different columns, like you have below.

Filter Values:
(E2) east
(E3 or G2 with below formula) rochester
....result is a list where all customers are in "rochester" and contain
"east" in the name.

 

[Max]

Let's back it up a bit <g> ..

Source data in cols A and B, from row2 down

Filter criteria input in E2:E3

E2: string to search in col A, eg: name
E3: string to search in col B, eg: city name

To return col A which satisfies both* criteria in col C
*ie requiring an AND(...) type of construct

Put in D2:
=IF(OR($E$2="",$E$3=""),"",IF(AND(ISNUMBER(SEARCH($E$2,A2)),ISNUMBER(SEARCH($E$3,B2))),ROW(A1),""))
(Leave D1 empty)

Put in C2:
=IF(ROW(A1)>COUNT(D),"",INDEX(A:A,SMALL(D,ROW(A1))+1))

(The "+1" is just an arithmetic adjustment as source data is from row2 down)

Select C22, fill down to cover the max expected extent of source data. Col
C returns the required results.

 

[Josh O.]

Very Close.

Is it possible to have:
the Filter range (E2:E10) for Customer Name and another range (ie - F2:F10)
for the City...and use the AND function to account for multiple criteria.

So that you end up with: If the Customer Name (column A) contains "east" OR
"west" AND the City (column B) contains "Buffalo" OR "Rochester"

 

[Max]

Not easy .. but one way to extend it ..

Assuming filter criteria inputs would be made within E2:F3, viz:

E2:E3 houses strings to search in col A, eg: name
F2:F3 houses strings to search in col B, eg: city name

Replace the earlier formula in D2 with:

=IF(COUNTBLANK($E$2:$F$3)=4,"",IF(OR(
AND(ISNUMBER(SEARCH($E$2,A2)),ISNUMBER(SEARCH($F$2,B2))),
AND(ISNUMBER(SEARCH($E$2,A2)),ISNUMBER(SEARCH($F$3,B2))),
AND(ISNUMBER(SEARCH($E$3,A2)),ISNUMBER(SEARCH($F$2,B2))),
AND(ISNUMBER(SEARCH($E$3,A2)),ISNUMBER(SEARCH($F$3,B2)))),
ROW(A1),""))

Copy D2 down. Col C formulas unchanged.