how to create row with data

[rafael]

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

 

[Ashish Mathur]

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]

ty for answering

i need an automated solution...

and i meant columns, not rows

"rafael" escreveu:

 

[Ashish Mathur]

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