Need help Excel 2002 compare function

[WJW]

I'm in need of expertise selecting specific records from 1 workbook that
have matching key fields in the records of a second workbook:

I have a workbook (Student_DB Database.xls) with a worksheet (New_MSTR) that
contains 225 student names and other field data for students that graduated
from our school. The two name fields are "First_Name:" (column G) and
"Last_Name" (Column F)

I have a separate second workbook (CNA_Registry.xls) with one worksheet
(Registry) that contains 32,424 names (First_Name: ; Last_Name listing all
students-statewide that took & passed State Boards after graduating from all
schools in the state. It too has two name fields designated as
"First_Name:" (column B) and "Last_Name" (column A)

I would like to identify & select (or highlight) only "our students" in the
2nd workbook using the "First_Name" & "Last_Name" fields as my sort
criteria. This will tell me how many of our student graduates went on to
take the State Boards, and what their license number is (license number is
designated as "License Number" (Column M) in second workbook.

Unfortunately, I can not seem to get the formula right. Any help would be
greatly appreciated!

Respectfully,

Wm. Whatley
(e-mail address removed)

" My dog is so smart he can smell dinner 30 minutes before its made!"

 

[mikebres]

You might try using an array formula. Something like this one:

=SUM(IF(First&Last=A2&B2,License))

You would need to define the First, Last, and License as range names that
refer to the static data in your CNA_Registry.xls. A2 and B2 would be the
list of first names and License would be the result. An array formula needs
to be entered using the CSE (Control Shift Enter) key combination.

Mike

 

[Ken Johnson]

I'm in need of expertise selecting specific records from 1 workbook that
have matching key fields in the records of a second workbook:

I have a workbook (Student_DB Database.xls) with a worksheet (New_MSTR) that
contains 225 student names and other field data for students that graduated
from our school. The two name fields are "First_Name:" (column G) and
"Last_Name" (Column F)

I have a separate second workbook (CNA_Registry.xls) with one worksheet
(Registry) that contains 32,424 names (First_Name: ; Last_Name listing all
students-statewide that took & passed State Boards after graduating from all
schools in the state. It too has two name fields designated as
"First_Name:" (column B) and "Last_Name" (column A)

I would like to identify & select (or highlight) only "our students" in the
2nd workbook using the "First_Name" & "Last_Name" fields as my sort
criteria. This will tell me how many of our student graduates went on to
take the State Boards, and what their license number is (license number is
designated as "License Number" (Column M) in second workbook.

Unfortunately, I can not seem to get the formula right. Any help would be
greatly appreciated!

Respectfully,

Wm. Whatley
(e-mail address removed)

" My dog is so smart he can smell dinner 30 minutes before its made!"

Assuming headings occupy only 1 row on both workbooks...

=SUMPRODUCT(--([CNA_Registry.xls]Registry!$A$2:$A$32425=F2),--
([CNA_Registry.xls]Registry!$B$2:$B$32425=G2),
([CNA_Registry.xls]Registry!$M$2:$M$32425))

will return 0 for your students not on Registry and License Number for
those on the Registry.

Ken Johnson

 

[WJW]

Thanks Ken!.....Your the Man! Worked Great!

Thanks also to Mike for responding

Wm Whatley

 

[Ken Johnson]

Thanks Ken!.....Your the Man! Worked Great!

Thanks also to Mike for responding

Great!
Thanks for the feedback.