 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Need help Excel 2002 compare function
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 quasho@comcast.net " My dog is so smart he can smell dinner 30 minutes before its made!" 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 > I'm in need of expertise selecting specific records from 1 workbook that > have matching key fields in the records of a second workbook: [quoted text clipped - 25 lines] > > " My dog is so smart he can smell dinner 30 minutes before its made!" Thanks Ken!.....Your the Man! Worked Great! Thanks also to Mike for responding Wm Whatley > You might try using an array formula. Something like this one: > [quoted text clipped - 44 lines] >> >> " My dog is so smart he can smell dinner 30 minutes before its made!" > Thanks Ken!.....Your the Man! Worked Great! > [quoted text clipped - 48 lines] > > >> " My dog is so smart he can smell dinner 30 minutes before its made!" Great! Thanks for the feedback. > I'm in need of expertise selecting specific records from 1 workbook that > have matching key fields in the records of a second workbook: [quoted text clipped - 25 lines] > > " 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.