Compare Data in two Separate Worksheets

I have two different data lists that I need to compare to see if information
from one list also appears in the second. The unique identifier (similar to
a social security number) appears in column C of both spreadsheets. Is there
any way that I can write a formula that will give me an output of "Yes" if
the data appears in spreadsheet 1 but not in spreadsheet 2?

In Sheet1, enter:
=IF(ISNA(MATCH(C1,Sheet2!C:C,0)),"yes","")

HTH
--
AP

"mary s" <[email protected]> a écrit dans le message de news:
[email protected]...
>I have two different data lists that I need to compare to see if
>information
> from one list also appears in the second. The unique identifier (similar
> to
> a social security number) appears in column C of both spreadsheets. Is
> there
> any way that I can write a formula that will give me an output of "Yes" if
> the data appears in spreadsheet 1 but not in spreadsheet 2?

you can try the vlookup function. it will pull the data from one sheet and
match it up to the second sheet. on one of the sheets insert a blank column
next to column c (if this is the column that has the common factor on both
sheets). click in a cell in column d. go to 'insert' then function. under
category select all and find 'vlookup'. select it.

Lookup value - this is your common factor which i believe is column c.
select a cell from column c.

table array - select the second sheet. find the column that has the
information you
want to pull over to the first sheet. click, hold and highlight that column
and drag over to the column with your common factor.

col_index_num - this is a little tricky. on the second sheet your common
factor column is considered column 1 because it was the furthest left column.
from column c you have to count by column until you get to the column with
the information you want to pull in. for exampl if column g was the info you
wanted to pull in then your col_index_num is 4 because g is 4 columns over
from c.

range lookup is always going to be false.

it should have pulled in your info from sheet two and placed it in sheet
one, column d, whatever cell you were originally in. you can copy and paste
the formula into all of the other cells in column d.

fyi - when you use a function like this it shows the cells saved as the
entire formula which make syour spreadsheet massive. once you copy the
formula into all of your cells go ahead and highlight the entire column and
copy then paste special values. this will minimize your file size.

hope this helps!!!!







"mary s" wrote:

> I have two different data lists that I need to compare to see if information
> from one list also appears in the second. The unique identifier (similar to
> a social security number) appears in column C of both spreadsheets. Is there
> any way that I can write a formula that will give me an output of "Yes" if
> the data appears in spreadsheet 1 but not in spreadsheet 2?

This didn't work as well as i had hoped. Is there a way that I can use Excel
to look at both columns in their entirety to tell me if there is a value
missing from one spreadsheet to the next? If my items are in a different
order from one spreadsheet to the next, this doesn't appear to be very
helpful.

"scraig" wrote:

> you can try the vlookup function. it will pull the data from one sheet and
> match it up to the second sheet. on one of the sheets insert a blank column
> next to column c (if this is the column that has the common factor on both
> sheets). click in a cell in column d. go to 'insert' then function. under
> category select all and find 'vlookup'. select it.
>
> Lookup value - this is your common factor which i believe is column c.
> select a cell from column c.
>
> table array - select the second sheet. find the column that has the
> information you
> want to pull over to the first sheet. click, hold and highlight that column
> and drag over to the column with your common factor.
>
> col_index_num - this is a little tricky. on the second sheet your common
> factor column is considered column 1 because it was the furthest left column.
> from column c you have to count by column until you get to the column with
> the information you want to pull in. for exampl if column g was the info you
> wanted to pull in then your col_index_num is 4 because g is 4 columns over
> from c.
>
> range lookup is always going to be false.
>
> it should have pulled in your info from sheet two and placed it in sheet
> one, column d, whatever cell you were originally in. you can copy and paste
> the formula into all of the other cells in column d.
>
> fyi - when you use a function like this it shows the cells saved as the
> entire formula which make syour spreadsheet massive. once you copy the
> formula into all of your cells go ahead and highlight the entire column and
> copy then paste special values. this will minimize your file size.
>
> hope this helps!!!!
>
>
>
>
>
>
>
> "mary s" wrote:
>
> > I have two different data lists that I need to compare to see if information
> > from one list also appears in the second. The unique identifier (similar to
> > a social security number) appears in column C of both spreadsheets. Is there
> > any way that I can write a formula that will give me an output of "Yes" if
> > the data appears in spreadsheet 1 but not in spreadsheet 2?