Cell formatting for Vlookup to work

[msd_2008]

Hi,

I have 2 lists of names of people and I want to run a vlookup check to see if all names are covered in both the lists or there are any which are missing from either of the lists. While using the Vlookup option I realised that the cell format has to be the same for comparison for eg: In column A if the name is Joe Blogg and in column B the name is Blogg, Joe or J Blogg then vlookup is unable to identify them as same. However, the data is huge and manually arranging the names all again is very very time consuming.
Is there an option to ensure that the cell formats for both columns are same irrespective of their individual texts? I mean to say, that can Joe Blogg cell format be copied so that Blogg, Joe automatically becomes Blogg Joe - i.e without the comma. With this the texts shall remain independant but their arrangement and format will be exactly same. Thats when the vlookup will work......or is there any other way that I could verify both the column name lists ?

Thanks
MSD

[stanleydgromjr]

msd_2008,

Welcome to the Excel Forum.

Can we see your workbook?

Click on New Post, then scroll down and see Manage Attachments.

[msd_2008]

Hi,

I have just created 2 short lists as the actual data I am working on is sensitive.
Can you help me understand how will the vlookup work in this example? My primary list is A and I am trying to match it with B. So, basically I need to know which names from list A show up on list B. And I want a function which will ensure the same format in both the lists, so that vlookup will be able to identify the same names eg: Bruce Willis in List A vs Willis, Bruce in List B or Lawrence Fishborne in List A vs L Fishborne in List B. What can be done to bring a same format across all cells with just one simple function?

Thanks
MSD

[stanleydgromjr]

msd_2008,

Here is a manual process (for fixing the names in ListB) that will get you closer to your results.

Detach/open the attached workbook "Manually fix NameListB - per example sheets - msd_2008 - SDG10.xls".

Read all the areas with background colors.