Tracking data

[Guest]

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.

 

[Guest]

Hi

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

Andy.

 

[random1970]

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.

 

[Guest]

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.