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

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



"dardiaca" wrote:

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

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

"Mike H" wrote:

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



"dardiaca" wrote:

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

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

"dardiaca" wrote in message
...
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

"Mike H" wrote:

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



"dardiaca" wrote:

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

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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Mike's formula works fine. Perhaps you'd better read the instructions
again?
--
David Biddulph

"dardiaca" wrote in message
...
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

"Mike H" wrote:

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



"dardiaca" wrote:

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

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
"dardiaca" wrote:

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

"Mike H" wrote:

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



"dardiaca" wrote:

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

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?

Please don't post the same question multiple times.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

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

"Stan Brown" wrote:

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?

Please don't post the same question multiple times.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/