How to compare one column to another?

[Jim Bancroft]

Hi everyone,

I have two columns of strings, both in ascending order. Similar lists but
not identical. Are there macros or other steps I can take to compare column
A with column B, so that all entries in A that aren't in B wind up in column
C? Thanks!

 

[Guest]

Hello

If your list in column A is from A1 to say A6 and list in B is from B1:B6
then enter the following in C1 and copy it down to C6

=IF(COUNTIF($B$1:$B$6,A1)>0,"",A1)

Alok

 

[Jim Bancroft]

Hi,

Unfortunately the columns don't have the same number of rows. What's more,
some entries in column A aren't in column B, and vice versa.

 

[Guest]

Hi Jim,

The formula does not require the same number of rows in columns A and B.
For instance your column A could be A1:A1000 and entries in B from B1:B500.
Then the formula in C1 will become

=IF(COUNTIF($B$1:$B$500,A1)>0,"",A1)

but it will still need to be copied down till C1000.

Hence the column C must (in my solution) have the same number of rows as in
column A - though I have seen in this newsgroup solutions that ensure that
column C does not have blanks as in my solution but has only the missing
entries right at the top of the column.

Alok