Function

D

dardiaca

I have two lists: one with my entire population and the second with the ones
that subscribed a training course. Which function should I use to compare
both lists and highlight in a new column those who still need training?
Thanks
 
M

Mike H

With conditional formatting

In the top name of your entire population enter the conditional format

Formula is =COUNTIF(B$1:B$1000,A1)=0 and add a colour

Use format painter to paint format into all cells on that list.

Any names not in the list B1 to B1000 will be highlighted.

Mike
 
S

Stan Brown

Mon, 2 Jul 2007 03:42:01 -0700 from dardiaca
I have two lists: one with my entire population and the second with the ones
that subscribed a training course. Which function should I use to compare
both lists and highlight in a new column those who still need training?
Click to expand...

Please don't post the same question multiple times.
 
D

dardiaca

Sorry Mike, but this is not working.

Let's assume I have my entire population in Colum A.
a
b
c
c
e

In column B I have those who attended the course
b
c
e

How do I create a new column C or highlight in column A those who didn't
attend the course?
Thx again
 
D

David Biddulph

Mike's formula works fine. Perhaps you'd better read the instructions
again?
 
D

dardiaca

It was clearly a mistake and this is the first time I sign in the forum. But
thanks for your quick and useful answer.
 
M

Mike H

Hi,

I'm not sure why the CF solution didn't work, are you sure you entered it
correctly.
Full list of names in Col A
Attendees in Col B
Select A1 then

Format|Conditional formatting
select formula is and enter the formula
=COUNTIF(B$1:B$1000,A1)=0
Select a colour
Paint this into your full list of names with the format painter and
Names not in column B will be highlighted.

To create a list in column C enter this in C1 and drag dowwn to the length
of colmn B

=IF(ISERROR(MATCH(A1,$B$1:$B$100,0)),A1,"")

Mike
 
D

David Biddulph

One thing which might cause problems is if what you think are identical
contents in the 2 columns are not actually identical. You may, for example
be confused if one or other of the columns has spare spaces or other
non-printing characters.
 
Top