A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to search a cell that existing in another column



 
 
Thread Tools Display Modes
  #1  
Old March 27th, 2004, 11:40 PM
vivian
external usenet poster
 
Posts: n/a
Default 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?

Ads
  #2  
Old March 27th, 2004, 11:48 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2004, 09:00 AM
Aladin Akyurek
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2004, 09:02 AM
Aladin Akyurek
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2004, 09:10 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2004, 11:44 AM
Aladin Akyurek
external usenet poster
 
Posts: n/a
Default 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  
Old March 28th, 2004, 11:57 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 OfficeFrustration.
The comments are property of their posters.