I have two spreadsheets each with over 5 thousand rows of data. on each
spreadsheet, column K is personnel numbers. What I want to know, is can I
create formulas in workbook 1 which would show if the personnel numbers in
workbook 1 appear in workbook 2 (column k), rather than having to search each
personnel number manually.

Hi

With your name in A2, you could use something like this:
=COUNTIF(Sheet2!K2:K10000,A2)0

Andy.

"Greenback" wrote in message
...
I have two spreadsheets each with over 5 thousand rows of data. on each
spreadsheet, column K is personnel numbers. What I want to know, is can I
create formulas in workbook 1 which would show if the personnel numbers in
workbook 1 appear in workbook 2 (column k), rather than having to search
each
personnel number manually.

Try this. Insert a column after column K in the second workbook.
Assuming you have a header row and 5000 rows of data, type the
following formula into L2.

=COUNTIF('[Workbook1.xls]Sheet1'!$K$2:$K$5001,K2)

Where Workbook1, Sheet 1 is the workbook name and sheet name of the
first workbook which contains the data, and L2 is the first personnel
number in Workbook 2. If the number exists in column K of Workbook 1,
the count will be 1. If it doesn't, it will be 0.

Fill down 5000 cells, and you've got your check. I wouldn't want to
check manually!

Hope this helps. Let me know how it goes.


--
random1970
------------------------------------------------------------------------
random1970's Profile: http://www.excelforum.com/member.php...o&userid=32112
View this thread: http://www.excelforum.com/showthread...hreadid=519270

Cheers Random1970......that formula did the trick. I can now see in one
workbook how many times a personnel number appears in another book.

Cheers also to Andy.

"random1970" wrote:


Try this. Insert a column after column K in the second workbook.
Assuming you have a header row and 5000 rows of data, type the
following formula into L2.

=COUNTIF('[Workbook1.xls]Sheet1'!$K$2:$K$5001,K2)

Where Workbook1, Sheet 1 is the workbook name and sheet name of the
first workbook which contains the data, and L2 is the first personnel
number in Workbook 2. If the number exists in column K of Workbook 1,
the count will be 1. If it doesn't, it will be 0.

Fill down 5000 cells, and you've got your check. I wouldn't want to
check manually!

Hope this helps. Let me know how it goes.


--
random1970
------------------------------------------------------------------------
random1970's Profile: http://www.excelforum.com/member.php...o&userid=32112
View this thread: http://www.excelforum.com/showthread...hreadid=519270