Hi,
Try the following steps:
1. Select the first range of name and convert it to a List (Ctrl+L).
2. Now assign a name to the first range of names, say Name1.
3. Follow steps 1 and 2 for the second range of names as well. Just assign
the second range a different name, say Name2
4. Save the file and save it on the desktop
5. Now click on any blank cell and go to:
a. Excel 2007 - Data > From Other Sources > Microsoft Query
b. Ecel 2003 - Data > Import External data > New Database query
6. In the Choose Data Source box, select Excel files. Click on OK
7. Select the Excel file (which you just saved on the desktop) and click on
OK
8. In the query wizard - choose columns, select Name1 and click on the
right arrow. Do the same for Name2. Click on Next
9. You will get message saying that the query wizard cannot continue.
Click on OK.
10. You will see 2 columns of names
11. Now go to Table > Joins and just click on Add and Close
12. Now remove the second column by clicking on any one entry and then go
to Records > Remove column. You will now see all the common entries.
13. File > Return Data to Microsoft Office Excel
14. In the box that comes up, select Properties and check the box for
Refresh every and select 1 minute. Also, check the box for Refresh when
opening the file. Click on OK, click on OK
You will not see all the common entries.
Hope this helps.
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com