I have two columns that I want to compare with each other. The column on the
right has extra values in it that the column on the left does not. I want to
identity the values in the right hand column that are not in the left hand
column. Like so...

(Column A) (Column B)
(Row 1) B0001 B0001
(Row 2) B0002 B0002
(Row 3) B0003 B0003
(Row 4) B0004 B0004
(Row 5) B0005 B0005
(Row 6) C0001 B0006
(Row 7) C0002 B0007
(Row 8) C0003 B0008
(Row 9) C0004 B0009
(Row 10) C0005 C0001
(Row 11) C0002
(Row 12) C0003
(Row 13) C0004
(Row 14) C0005
(Row 15) C0006
(Row 16) C0007
(Row 17) C0008

I would like to create a column C that says whether the value in column B is
in column A.

This probably sounds quite simple to you guys but I can't for the life of me
work out how to do this. It all seems to go a bit wrong on row 7 where the
same values aren't aligned with each other.

In reality this spreadsheet has thousands of rows so if I could get this
formula to work it would be a great great help to me.

Thank you so much in advance.

Clare,

This will return TRUE if the value from B is not found in A:

In C1, then copy down:
=ISERROR(MATCH(B1,A:A,FALSE))

Or, to use a different flag:

=IF(ISERROR(MATCH(B1,A:A,FALSE)),"Not Found in Column A","")


HTH,
Bernie
MS Excel MVP


"ClareMylrea" wrote in message
...
I have two columns that I want to compare with each other. The column on the
right has extra values in it that the column on the left does not. I want to
identity the values in the right hand column that are not in the left hand
column. Like so...

(Column A) (Column B)
(Row 1) B0001 B0001
(Row 2) B0002 B0002
(Row 3) B0003 B0003
(Row 4) B0004 B0004
(Row 5) B0005 B0005
(Row 6) C0001 B0006
(Row 7) C0002 B0007
(Row 8) C0003 B0008
(Row 9) C0004 B0009
(Row 10) C0005 C0001
(Row 11) C0002
(Row 12) C0003
(Row 13) C0004
(Row 14) C0005
(Row 15) C0006
(Row 16) C0007
(Row 17) C0008

I would like to create a column C that says whether the value in column B is
in column A.

This probably sounds quite simple to you guys but I can't for the life of me
work out how to do this. It all seems to go a bit wrong on row 7 where the
same values aren't aligned with each other.

In reality this spreadsheet has thousands of rows so if I could get this
formula to work it would be a great great help to me.

Thank you so much in advance.

HI Clare

In C1 enter
=IF(COUNTIF(B:B,A1)0,"Exists","")
Copy down column C as far as required

--
Regards
Roger Govier

"ClareMylrea" wrote in message
...
I have two columns that I want to compare with each other. The column on
the
right has extra values in it that the column on the left does not. I want
to
identity the values in the right hand column that are not in the left hand
column. Like so...

(Column A) (Column B)
(Row 1) B0001 B0001
(Row 2) B0002 B0002
(Row 3) B0003 B0003
(Row 4) B0004 B0004
(Row 5) B0005 B0005
(Row 6) C0001 B0006
(Row 7) C0002 B0007
(Row 8) C0003 B0008
(Row 9) C0004 B0009
(Row 10) C0005 C0001
(Row 11) C0002
(Row 12) C0003
(Row 13) C0004
(Row 14) C0005
(Row 15) C0006
(Row 16) C0007
(Row 17) C0008

I would like to create a column C that says whether the value in column B
is
in column A.

This probably sounds quite simple to you guys but I can't for the life of
me
work out how to do this. It all seems to go a bit wrong on row 7 where
the
same values aren't aligned with each other.

In reality this spreadsheet has thousands of rows so if I could get this
formula to work it would be a great great help to me.

Thank you so much in advance.

Thank you SO MUCH for your help with this. It works perfectly.

You have made my day! :-) :-) :-)

"Bernie Deitrick" wrote:

Clare,

This will return TRUE if the value from B is not found in A:

In C1, then copy down:
=ISERROR(MATCH(B1,A:A,FALSE))

Or, to use a different flag:

=IF(ISERROR(MATCH(B1,A:A,FALSE)),"Not Found in Column A","")


HTH,
Bernie
MS Excel MVP


"ClareMylrea" wrote in message
...
I have two columns that I want to compare with each other. The column on the
right has extra values in it that the column on the left does not. I want to
identity the values in the right hand column that are not in the left hand
column. Like so...

(Column A) (Column B)
(Row 1) B0001 B0001
(Row 2) B0002 B0002
(Row 3) B0003 B0003
(Row 4) B0004 B0004
(Row 5) B0005 B0005
(Row 6) C0001 B0006
(Row 7) C0002 B0007
(Row 8) C0003 B0008
(Row 9) C0004 B0009
(Row 10) C0005 C0001
(Row 11) C0002
(Row 12) C0003
(Row 13) C0004
(Row 14) C0005
(Row 15) C0006
(Row 16) C0007
(Row 17) C0008

I would like to create a column C that says whether the value in column B is
in column A.

This probably sounds quite simple to you guys but I can't for the life of me
work out how to do this. It all seems to go a bit wrong on row 7 where the
same values aren't aligned with each other.

In reality this spreadsheet has thousands of rows so if I could get this
formula to work it would be a great great help to me.

Thank you so much in advance.

Thanks Roger :-)

"Roger Govier" wrote:

HI Clare

In C1 enter
=IF(COUNTIF(B:B,A1)0,"Exists","")
Copy down column C as far as required

--
Regards
Roger Govier

"ClareMylrea" wrote in message
...
I have two columns that I want to compare with each other. The column on
the
right has extra values in it that the column on the left does not. I want
to
identity the values in the right hand column that are not in the left hand
column. Like so...

(Column A) (Column B)
(Row 1) B0001 B0001
(Row 2) B0002 B0002
(Row 3) B0003 B0003
(Row 4) B0004 B0004
(Row 5) B0005 B0005
(Row 6) C0001 B0006
(Row 7) C0002 B0007
(Row 8) C0003 B0008
(Row 9) C0004 B0009
(Row 10) C0005 C0001
(Row 11) C0002
(Row 12) C0003
(Row 13) C0004
(Row 14) C0005
(Row 15) C0006
(Row 16) C0007
(Row 17) C0008

I would like to create a column C that says whether the value in column B
is
in column A.

This probably sounds quite simple to you guys but I can't for the life of
me
work out how to do this. It all seems to go a bit wrong on row 7 where
the
same values aren't aligned with each other.

In reality this spreadsheet has thousands of rows so if I could get this
formula to work it would be a great great help to me.

Thank you so much in advance.