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

 

[dranon]

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

Like:

mary will

or like:

mary
will

????

 

[rafael]

ty

i have excel 2000

what i want is all names in a single row, without blank cells, like:
mary
will

ty

 

[rafael]

sorry, i meant all in a single column

Like:

mary will

or like:

mary
will

????

 

[dranon]

Can I convince you to put in a helper column?

Inset a new column A, before your existing column A, so that your
existing columns are no longer A and B, but instead they are B and C.

In your new column A, enter in row 1:

=if(c1="blue",1,0)

Enter in row 2 of your new column A:

=if(c2="blue",a1+1,a1)

Copy that down to as many rows as you have data in columns B and C

In column D, enter in row 1:

=IF(ISERROR(VLOOKUP(ROW(),$A$1:$C$9999,2,0)),"",VLOOKUP(ROW(),$A$1:$C$9999,2,0))

Copy that down to as many rows as you have data in columns B and C

You can make it more general by substituting a cell reference for
"blue".

 

[rafael]

ty dranon

i have excel 2000 in portuguese PT

i cant find the function VLOOKUP... ....

 

[David Heaton]

ty dranon

i have excel 2000 in portuguese PT

i cant find the function VLOOKUP... ....










- Show quoted text -

you could replace the VLOOKUP portion with

=INDEX($A$1:$A$9999,MATCH(ROW(),$C$1:$C$999,0),1)

you will end up with blank rows though as if either the VLOOKUP or
INDEX function cant find a color it inserts an ""

Why not use filter so that you only see those names with the color you
have filtered by?


regards

David

 

[Don Guillett]

data>filter>autofilter>>>>

 

[keiji kounoike]

Assume your data of name reside in "A1:A100" and color of data in
"B1:B100", put the following formula into any cell you like and copy it
downward as many as you like. this formula is array formula, so you need
to enter it with Shift+Ctrl+Enter. after this, copy that cell.

{=IF(ISERROR(INDEX(A$1:A$100,SMALL(IF((B$1:B$100="blue"),ROW($1:$100),""),ROW(A1)))),"",INDEX(A$1:A$100,SMALL(IF((B$1:B$100="blue"),ROW($1:$100),""),ROW(A1))))}

{} is added automatically when you put the formula and not just push
enter key but with Shift+Ctrl+Enter.

Keiji

 

[rafael]

hi again

you said:

Why not use filter so that you only see those names with the color you
have filtered by?

because i have other important data in same sheet, and filter hides it too
.... (

 

[David Heaton]

hi again

you said:


because i have other important data in same sheet, and filter hides it too
... (







- Show quoted text -

Rafael,

Have you thought about a pivot table?

David