Index Match to add employee number
 

Hi,

I've got two sheets of data.

Sheet1 Data contains employee info
Col A = Employee number Alphanumeric text field = DataERN
Col B = Forename
Col C = Middle
Col D= Surname
Col E = DOB

Sheet 2 Details contains more info from a different source so there is no
employee number
Col A = Name - mostly surname followed by forename but some mid names
Col B = DOB

So I want to give all the employees in Details an Employee Number from Data.
I've concatenated B, D and E in Data(Helper). In Details I've split A with
Text to Cols to take out middle name and concatenated forename surname and
DOB (F)to match string in Data.

I would usually use Index and Match

=INDEX(DataERN,MATCH($F2,Helper,0))

but the match type 0, I believe finds the first exact match.

1. What happens if there is more than 1 exact match? This is a definite
possibility
2. It will also become more of a possibility if I try to account for
differences such as mistyped data, changes in surname etc. I'm planning to
put some additional columns in with different combinations of concatenated
names and DOB.

Is there any way I can get it to return all possible matches?
I'm guessing no but hoping yes :-)

Any ideas about ways of doing this would be very welcome.

Cheers
Diddy

Index Match to add employee number
 

Hi,

You may want to refer to my article here -
http://office.microsoft.com/en-us/ex...CL100570551033

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Diddy" wrote in message
...
Hi,

I've got two sheets of data.

Sheet1 Data contains employee info
Col A = Employee number Alphanumeric text field = DataERN
Col B = Forename
Col C = Middle
Col D= Surname
Col E = DOB

Sheet 2 Details contains more info from a different source so there is no
employee number
Col A = Name - mostly surname followed by forename but some mid names
Col B = DOB

So I want to give all the employees in Details an Employee Number from
Data.
I've concatenated B, D and E in Data(Helper). In Details I've split A with
Text to Cols to take out middle name and concatenated forename surname and
DOB (F)to match string in Data.

I would usually use Index and Match

=INDEX(DataERN,MATCH($F2,Helper,0))

but the match type 0, I believe finds the first exact match.

1. What happens if there is more than 1 exact match? This is a definite
possibility
2. It will also become more of a possibility if I try to account for
differences such as mistyped data, changes in surname etc. I'm planning to
put some additional columns in with different combinations of concatenated
names and DOB.

Is there any way I can get it to return all possible matches?
I'm guessing no but hoping yes :-)

Any ideas about ways of doing this would be very welcome.

Cheers
Diddy

Index Match to add employee number
 

Thanks Ashish,

I'll have a look and then come back
Looks as though it may be a "fiddly" job after all

Cheers
Diddy
"Ashish Mathur" wrote:

Hi,

You may want to refer to my article here -
http://office.microsoft.com/en-us/ex...CL100570551033

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Diddy" wrote in message
...
Hi,

I've got two sheets of data.

Sheet1 Data contains employee info
Col A = Employee number Alphanumeric text field = DataERN
Col B = Forename
Col C = Middle
Col D= Surname
Col E = DOB

Sheet 2 Details contains more info from a different source so there is no
employee number
Col A = Name - mostly surname followed by forename but some mid names
Col B = DOB

So I want to give all the employees in Details an Employee Number from
Data.
I've concatenated B, D and E in Data(Helper). In Details I've split A with
Text to Cols to take out middle name and concatenated forename surname and
DOB (F)to match string in Data.

I would usually use Index and Match

=INDEX(DataERN,MATCH($F2,Helper,0))

but the match type 0, I believe finds the first exact match.

1. What happens if there is more than 1 exact match? This is a definite
possibility
2. It will also become more of a possibility if I try to account for
differences such as mistyped data, changes in surname etc. I'm planning to
put some additional columns in with different combinations of concatenated
names and DOB.

Is there any way I can get it to return all possible matches?
I'm guessing no but hoping yes :-)

Any ideas about ways of doing this would be very welcome.

Cheers
Diddy