If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to search a cell that existing in another column
I need to compare column B and column C. If the cell in
column B exist in Column C, hight light it. Else nothign happened. There are 120 values in column B and 250 values in column C. How can i do this? is there any function that i can use? |
#2
|
|||
|
|||
how to search a cell that existing in another column
Hi
try the following - select your cells in column B (assumption: you start in row 1) - goto 'Format - Conditional Format' - enter the following formula in this dialog: =COUNTIF($C$1:$C$300,B1)0 - choose a format for these cells you may also have a look at http://www.cpearson.com/excel/duplic...tractingCommon -- Regards Frank Kabel Frankfurt, Germany "vivian" schrieb im Newsbeitrag ... I need to compare column B and column C. If the cell in column B exist in Column C, hight light it. Else nothign happened. There are 120 values in column B and 250 values in column C. How can i do this? is there any function that i can use? |
#3
|
|||
|
|||
how to search a cell that existing in another column
"Frank Kabel" wrote in message
... Hi try the following - select your cells in column B (assumption: you start in row 1) - goto 'Format - Conditional Format' - enter the following formula in this dialog: =COUNTIF($C$1:$C$300,B1)0 - choose a format for these cells [...] Just =MATCH(B1,$C$1:$C$300) would be faster though. |
#4
|
|||
|
|||
how to search a cell that existing in another column
I meant to say:
=MATCH(B1,$C$1:$C$300,0) "Aladin Akyurek" wrote in message ... "Frank Kabel" wrote in message ... Hi try the following - select your cells in column B (assumption: you start in row 1) - goto 'Format - Conditional Format' - enter the following formula in this dialog: =COUNTIF($C$1:$C$300,B1)0 - choose a format for these cells [...] Just =MATCH(B1,$C$1:$C$300) would be faster though. |
#5
|
|||
|
|||
how to search a cell that existing in another column
"Aladin Akyurek" schrieb im Newsbeitrag ... "Frank Kabel" wrote in message ... Hi try the following - select your cells in column B (assumption: you start in row 1) - goto 'Format - Conditional Format' - enter the following formula in this dialog: =COUNTIF($C$1:$C$300,B1)0 - choose a format for these cells [...] Just =MATCH(B1,$C$1:$C$300) would be faster though. Hi Aladin always impressed about your knowledge about perfomance issues! I thought COUNTIF would be faster. Have you some details about the performance difference (or a link to a previous discussion) Regards Frank |
#6
|
|||
|
|||
how to search a cell that existing in another column
"Frank Kabel" wrote in message ... "Aladin Akyurek" schrieb im Newsbeitrag ... "Frank Kabel" wrote in message ... Hi try the following - select your cells in column B (assumption: you start in row 1) - goto 'Format - Conditional Format' - enter the following formula in this dialog: =COUNTIF($C$1:$C$300,B1)0 - choose a format for these cells [...] Just =MATCH(B1,$C$1:$C$300) would be faster though. Hi Aladin always impressed about your knowledge about perfomance issues! I thought COUNTIF would be faster. The formula should be: =MATCH(B1,$C$1:$C$300,0) CountIf needs to look at the whole range C1:C300 for every value. MATCH will stop looking further after a success. Since we are talking of using in conditional formatting, the CountIf formula can be shortened to: =COUNTIF($C$1:$C$300,B1) The speed difference will still hold though, excepting perhaps no success cases. Have you some details about the performance difference (or a link to a previous discussion) [...] http://www.mrexcel.com/board2/viewtopic.php?t=40233 |
#7
|
|||
|
|||
how to search a cell that existing in another column
The formula should be:
=MATCH(B1,$C$1:$C$300,0) CountIf needs to look at the whole range C1:C300 for every value. MATCH will stop looking further after a success. Since we are talking of using in conditional formatting, the CountIf formula can be shortened to: =COUNTIF($C$1:$C$300,B1) The speed difference will still hold though, excepting perhaps no success cases. Have you some details about the performance difference (or a link to a previous discussion) [...] http://www.mrexcel.com/board2/viewtopic.php?t=40233 Aladin thanks for the information Frank |
Thread Tools | |
Display Modes | |
|
|