comparing columns
 

I have a master Excel worksheet, which has six columns:- C:D:E:F:G:H and
there are approximately one thousand rows. On a daily basis I import data
into Column A. of this master worksheet. The data consists of lots of
different 10 digit numbers, so I have a different 10 digit number in each row
of Column A., an example of which follows:-

A

1234567896
1721626196
3333445556
7768754321
9675332699

The amount of rows that I import also varies daily, it can be as low as five
rows of 10 digit numbers imported, or approximately 999 rows of 10 digit
numbers imported.

The rest of the worksheet is as follows:-

Column C has four numbers
Column D has text
Column E has text
Column F. has different 10 digit numbers in every row
Column G has text
Column H has text


What I would like to do, is to compare Column A., to column F. and if any 10
digit number in Column A. is the same as any 10 digit number in column F.
then I would like to highlight that number, in both Column A. and in column
F. with the colour green, so I can see at a glance, what numbers in Column A.
are also in column F.
So to sum up, if I import 20 x 10 digit numbers into column A., and those
20 numbers are in column F. then all 20 numbers in column A. and all 20
matching numbers in column F. would turn green, Either the font or the cell
background, whatever it is easiest., but the colour of the rest of the
unmatched numbers in column F. and column A. would remain unchanged. I have
not had much experience using Excel, and to assist me I have a couple of
library books on the subject, but there is nothing in the books that will
help me with this problem, hence my post here in the hope that somebody could
help me please.

comparing columns
 

Select all the entries in A (or click on the A column header)
Use Format | Conditional Formatting;
specify Formula IS: =COUNTIF(F:F,A1) and hit the Format button then give the
cell a fill colour (pattern) and/or a font colour as required
Do the same with F using =COUNTIF(A:A,F1)

Explanation: the formula will evaluate to 0 (no matches) or a positive
number; but Conditional Formatting using Boolean logic so 0 is treated as
FALSE and positive numbers as TRUE. Hence TRUE (one or matches found) will
switch on the colour.

If you do this daily, why not record a macro to do it?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"mel" wrote in message
...
I have a master Excel worksheet, which has six columns:- C:D:E:F:G:H and
there are approximately one thousand rows. On a daily basis I import data
into Column A. of this master worksheet. The data consists of lots of
different 10 digit numbers, so I have a different 10 digit number in each
row
of Column A., an example of which follows:-

A

1234567896
1721626196
3333445556
7768754321
9675332699

The amount of rows that I import also varies daily, it can be as low as
five
rows of 10 digit numbers imported, or approximately 999 rows of 10 digit
numbers imported.

The rest of the worksheet is as follows:-

Column C has four numbers
Column D has text
Column E has text
Column F. has different 10 digit numbers in every row
Column G has text
Column H has text


What I would like to do, is to compare Column A., to column F. and if any
10
digit number in Column A. is the same as any 10 digit number in column F.
then I would like to highlight that number, in both Column A. and in
column
F. with the colour green, so I can see at a glance, what numbers in Column
A.
are also in column F.
So to sum up, if I import 20 x 10 digit numbers into column A., and those
20 numbers are in column F. then all 20 numbers in column A. and all 20
matching numbers in column F. would turn green, Either the font or the
cell
background, whatever it is easiest., but the colour of the rest of the
unmatched numbers in column F. and column A. would remain unchanged. I
have
not had much experience using Excel, and to assist me I have a couple of
library books on the subject, but there is nothing in the books that will
help me with this problem, hence my post here in the hope that somebody
could
help me please.

comparing columns
 

This formula has achieved exactly what I wanted to do in my master
worksheet, and consequently I would like to say a big thank you to you
Bernard, Your help has been very much appreciated, and without people like
you taking the time and effort to help us newbies out, then a lot of us would
fall by the wayside.
Once more ...... Many Thanks!

Regards.
Mel.

"Bernard Liengme" wrote:

Select all the entries in A (or click on the A column header)
Use Format | Conditional Formatting;
specify Formula IS: =COUNTIF(F:F,A1) and hit the Format button then give the
cell a fill colour (pattern) and/or a font colour as required
Do the same with F using =COUNTIF(A:A,F1)

Explanation: the formula will evaluate to 0 (no matches) or a positive
number; but Conditional Formatting using Boolean logic so 0 is treated as
FALSE and positive numbers as TRUE. Hence TRUE (one or matches found) will
switch on the colour.

If you do this daily, why not record a macro to do it?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"mel" wrote in message
...
I have a master Excel worksheet, which has six columns:- C:D:E:F:G:H and
there are approximately one thousand rows. On a daily basis I import data
into Column A. of this master worksheet. The data consists of lots of
different 10 digit numbers, so I have a different 10 digit number in each
row
of Column A., an example of which follows:-

A

1234567896
1721626196
3333445556
7768754321
9675332699

The amount of rows that I import also varies daily, it can be as low as
five
rows of 10 digit numbers imported, or approximately 999 rows of 10 digit
numbers imported.

The rest of the worksheet is as follows:-

Column C has four numbers
Column D has text
Column E has text
Column F. has different 10 digit numbers in every row
Column G has text
Column H has text


What I would like to do, is to compare Column A., to column F. and if any
10
digit number in Column A. is the same as any 10 digit number in column F.
then I would like to highlight that number, in both Column A. and in
column
F. with the colour green, so I can see at a glance, what numbers in Column
A.
are also in column F.
So to sum up, if I import 20 x 10 digit numbers into column A., and those
20 numbers are in column F. then all 20 numbers in column A. and all 20
matching numbers in column F. would turn green, Either the font or the
cell
background, whatever it is easiest., but the colour of the rest of the
unmatched numbers in column F. and column A. would remain unchanged. I
have
not had much experience using Excel, and to assist me I have a couple of
library books on the subject, but there is nothing in the books that will
help me with this problem, hence my post here in the hope that somebody
could
help me please.