help in countif formula.
 

i have this table, wherein i wanted to have output in column3 names of those
who appears in column1 and column2

i.e.

name1 name2
jasper aileen
rd jasper
jp norman
aimee jojo
cris aimee

but as i use this formula - =if(countif(a:a,"=" & b1)0,B1,""), this is what
happens,

name1 name2 name3
jasper aileen
rd jasper jasper
jp norman
aimee jojo
cris aimee aimee

is it possible for me to be able to line them properly at the top?

help in countif formula.
 

Put name3 in C1

Then select C2:C10, and in the formula bar enter

=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$20,$B$1:$B$20 )0,ROW($A1:$A20),""),ROW(
$A1:$A20))),"",
INDEX($B$1:$B$20,SMALL(IF(COUNTIF($A$1:$A$20,$B$1: $B$20)0,ROW($A1:$A20),"")
,ROW($A1:$A20))))

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

--
HTH

Bob Phillips

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

"martin" wrote in message
...
i have this table, wherein i wanted to have output in column3 names of
those
who appears in column1 and column2

i.e.

name1 name2
jasper aileen
rd jasper
jp norman
aimee jojo
cris aimee

but as i use this formula - =if(countif(a:a,"=" & b1)0,B1,""), this is
what
happens,

name1 name2 name3
jasper aileen
rd jasper jasper
jp norman
aimee jojo
cris aimee aimee

is it possible for me to be able to line them properly at the top?

help in countif formula.
 

thanks!!!!

im a happy person!!!

--
technical newbie


"Bob Phillips" wrote:

Put name3 in C1

Then select C2:C10, and in the formula bar enter

=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$20,$B$1:$B$20 )0,ROW($A1:$A20),""),ROW(
$A1:$A20))),"",
INDEX($B$1:$B$20,SMALL(IF(COUNTIF($A$1:$A$20,$B$1: $B$20)0,ROW($A1:$A20),"")
,ROW($A1:$A20))))

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

--
HTH

Bob Phillips

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

"martin" wrote in message
...
i have this table, wherein i wanted to have output in column3 names of
those
who appears in column1 and column2

i.e.

name1 name2
jasper aileen
rd jasper
jp norman
aimee jojo
cris aimee

but as i use this formula - =if(countif(a:a,"=" & b1)0,B1,""), this is
what
happens,

name1 name2 name3
jasper aileen
rd jasper jasper
jp norman
aimee jojo
cris aimee aimee

is it possible for me to be able to line them properly at the top?

help in countif formula.
 

That's our goal <G

--
HTH

Bob Phillips

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

"martin" wrote in message
...
thanks!!!!

im a happy person!!!

--
technical newbie


"Bob Phillips" wrote:

Put name3 in C1

Then select C2:C10, and in the formula bar enter


=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$20,$B$1:$B$20 )0,ROW($A1:$A20),""),ROW(
$A1:$A20))),"",

INDEX($B$1:$B$20,SMALL(IF(COUNTIF($A$1:$A$20,$B$1: $B$20)0,ROW($A1:$A20),"")
,ROW($A1:$A20))))

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

--
HTH

Bob Phillips

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

"martin" wrote in message
...
i have this table, wherein i wanted to have output in column3 names of
those
who appears in column1 and column2

i.e.

name1 name2
jasper aileen
rd jasper
jp norman
aimee jojo
cris aimee

but as i use this formula - =if(countif(a:a,"=" & b1)0,B1,""), this
is
what
happens,

name1 name2 name3
jasper aileen
rd jasper jasper
jp norman
aimee jojo
cris aimee aimee

is it possible for me to be able to line them properly at the top?