imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Hi,

Assume that the data below is in range B4:C6. In B3, type name and in cell
C3 type Color. In cell C9, type color and in C10, type Blue. In cell E3,
type Name. Now go to Data Filter Advanced Filter and select Copy to
another location. In list range select B3:C6. In Criteria, select C9:C10.
In copy to box, select E3:E6 and click on OK.

Please note that this is not a dynamic solution I.e. if any entry changes in
range B4:C6 or in cell C10, the output in range E4:E6 will not change
automatically. You will have to rerun the advanced filter

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"rafael" wrote in message
...
imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

ty for answering

i need an automated solution...

and i meant columns, not rows :(

"rafael" escreveu:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Hi,

Try this. Suppose the data is in range A9:B11. Enter Blue in cell A13. In
cell B13, array enter (Ctrl+Shift+Enter) the following

=IF(ISERROR(INDEX($A$9:$B$11,SMALL(IF($B$9:$B$11=$ A$13,ROW($B$9:$B$11)),ROW(1:1))-ROW(B$8),1)),"",INDEX($A$9:$B$11,SMALL(IF($B$9:$B$ 11=$A$13,ROW($B$9:$B$11)),ROW(1:1))-ROW(B$8),1))

Now you can copy this formula down

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"rafael" wrote in message
...
imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you