Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
countif formula | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
COUNTIF formula problems | Excel Discussion (Misc queries) | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions |