Vlookup doesnt help

December 10, 2011 at 07:26:00
Specs: Windows 7

I have to regularly check if the names of the person are recorded correctly against their area. I use File A and check if the names in file B are correctly posted to the area based on file A.
However the format in the two files is different so I can not get results using vlookup,

for eg File A :
Person Area

john simpson transport
kate game healthcare

File B:

simpson, J transport
Game, kate healthcare

Is there any formulae/command/macro i can use to convert the names in same style or some other way using which i can match these names to check the person is being allocated to the correct area?

I use Excel version 2003and 2010.

Thanks v much

See More: Vlookup doesnt help

Report •

December 10, 2011 at 07:49:23
What you need is to apply Data Validation to both sheets.

See here for a quick intro:




Report •

December 10, 2011 at 08:16:33

Thanks for the response. I guess this would help if I was entering the data in the files. However I have to review these files and do not enter the data. And the two files are made by different sources and people , and I have to check to see that the names and area in file B have been entered as per name and area in file A.

Please help!

Report •

December 10, 2011 at 10:30:38
I have to review these files and do not enter the data. And the two files are made by different sources and people

Data validation is a tool that helps you control the kind of information that is entered in your worksheet.

Talk to the two sources that create the files and have them institute some type of data validation scheme.
Point out the it will save time & money.

How is your file setup, post an example with Column Letters & Headers
and row numbers.

As it stands, there are a number of different ways to input:

john simpson transport
J. simpson, Transport
Simpson, J. Transport
Simpson, John, Transport
John Simpson, Transport

Is this all one cell or in two adjacent cells?



Report •

Related Solutions

December 10, 2011 at 13:44:35
oh thanks, i will try using these. However I have asked the people previously but its sort of complicated as some of the data is used from on-line storages so the source can not be tracked and its not only for my files they prepare data, hence they can not change the format of the files.

Report •

December 10, 2011 at 13:46:25
and yes, these are in two seperate columns, under heading name and area

Report •

Ask Question