Multiple conditional list

[CEL]

Hello,

I am trying to auto generate a list on a worksheet based on MULTIPLE
conditions in
another i.e.

Worksheet 1 Contains the list below,

tree green america-north sequoia
tree green america-south cedar
tree green america-south tall cedar
tree red asia asiatictree

I want a list to be generated in worksheet2, based on multiple
criterias:
col1: tree
col2: green
col3: left(col3;7)=america

Result would look like:
sequoia row
cedar row
tall cedar row

I need to use a function to make it, not a macro.

I have read several post on using index & small, but I cannot make this
on working...

Thanks a lot for your help

[Max]

One play ..

Assuming source table in Sheet1, cols A to C, data from row2 down

In Sheet2,

Let's reserve C1:C3 for input of criteria for cols A to C
Input in C1:C3 : tree, green, america

Then

Put in A2:
=IF(ROW(A1)>COUNT(B:B),"",INDEX(Sheet1!D:D,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Put in B2:
=IF(COUNTBLANK($C$1:$C$3)>0,"",IF(AND(Sheet1!A2=$C$1,Sheet1!B2=$C$2,ISNUMBER(SEARCH($C$3,Sheet1!C2))),ROW(),""))
(Leave B1 empty)

Select A2:B2, copy down to say, B100,
cover the max expected extent of data in Sheet1

Col A will return the required results
(Hide away the criteria col B, if necess)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"CEL" wrote:
> Hello,
>
> I am trying to auto generate a list on a worksheet based on MULTIPLE
> conditions in
> another i.e.
>
> Worksheet 1 Contains the list below,
>
> tree green america-north sequoia
> tree green america-south cedar
> tree green america-south tall cedar
> tree red asia asiatictree
>
> I want a list to be generated in worksheet2, based on multiple
> criterias:
> col1: tree
> col2: green
> col3: left(col3;7)=america
>
> Result would look like:
> sequoia row
> cedar row
> tall cedar row
>
> I need to use a function to make it, not a macro.
>
> I have read several post on using index & small, but I cannot make this
> on working...
>
> Thanks a lot for your help
>
>

[Bob Phillips]

select a number of rows in a column that is the max number of possible
matches, and in the formula bar enter

=IF(ISERROR(SMALL(IF(($A$1:$A$20="tree")*($B$1:$B$20="green")*(LEFT($C$1:$C$
20,7)="america"),ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20="tree")*($B$1:$B$20="green")*(LEFT($C$1:
$C$20,7)="america"),ROW($A1:$A20),""),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy this block of cells across 4 columns.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CEL" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I am trying to auto generate a list on a worksheet based on MULTIPLE
> conditions in
> another i.e.
>
> Worksheet 1 Contains the list below,
>
> tree green america-north sequoia
> tree green america-south cedar
> tree green america-south tall cedar
> tree red asia asiatictree
>
> I want a list to be generated in worksheet2, based on multiple
> criterias:
> col1: tree
> col2: green
> col3: left(col3;7)=america
>
> Result would look like:
> sequoia row
> cedar row
> tall cedar row
>
> I need to use a function to make it, not a macro.
>
> I have read several post on using index & small, but I cannot make this
> on working...
>
> Thanks a lot for your help
>

[Max]

> Col A will return the required results

Results in col A will all be neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---