Prev: Unpivoting a worksheet
Next: Print and shrink automatically so that worksheet fits on paper?
From: Reg on 15 Apr 2010 11:27
I see a lot of people have aswered the specific question but I wondered where
this would end?

for example: Joe.Blogs or j.bloggs or joeblogs. or j.blogg (ad nauseam)

from the basic formula you are asking for a generalised data cleansing
solution and that is a world of pain - surely the inputter should take SOME
repsonsibility?


Reg Migrant

"John Smith" wrote:

> Apologies for the repeat thread, just there is so much traffic here that the
> last part of my query went without an answer.
>
> Sheet "Employee Data" holds usernames of about 1500 employees in the format
> "joe.bloggs" in column A and employee payroll numbers in the format 00001,
> 00002, etc in column B
>
> I am looking for a formula so that in another worksheet, where an employee
> types a name in the format "Joe", "Bloggs", "joe.bloggs" or Joe Bloggs" in
> the cell in column C, it will return the relevant payroll number in column
> D.
>
> If there is another employee called (say) Fred Bloggs and the employee just
> types in "Bloggs", I would like it to return some sort of error because
> there are two Bloggses.
>
> Data starts in row 3
>
> Currently I was looking at the following in D3 which was very kindly given
> and works quite well...
>
> =IF(COUNTIF('Employee
> Data'!A1:B1500,"*"&B2&"*")>1,"Error",IF(ISNA(VLOOKUP("*"&B2&"*",'Employee
> Data'!A1:B1500,2,FALSE)),"Employee not found",VLOOKUP("*"&B2&"*",'Employee
> Data'!A1:B1500,2,FALSE)))
>
> But n testing this, I have found that searching for 'Joe Bloggs' when the
> username is 'joe.bloggs' (with a separating dot) gives an "Employee not
> found" error. It works with all the other criteria I specified, just not
> this one.
>
> Is there anything I can add to it that will make it meet these criteria?
>
>
>
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Unpivoting a worksheet
Next: Print and shrink automatically so that worksheet fits on paper?