Returning text values in a database.

[kilianfoy]

Hi everyone,

I'm having some trouble in linking statistics into a small database I'm compiling and was hoping someone would be able to help me (or tell me if it's even possible!!)

I have a database containing statistical information for a group of employees' performance over the course of a month, which is collated from two different systems. In System A, the name of the employee is listed as "Joe Bloggs", whereas in System B, the name is "Bloggs, Joe". The turnaround of employees also means that the list is constantly changing, and I want to create something along the lines of a Match/Lookup formula that searches using Joe Bloggs as the parameter, and returns the second name of Bloggs, Joe in the adjacent field. Ideally this would use the Cell number as the reference as opposed to the name, since the employee list is constantly developing.

Is it possible or am I mad? I know it can be done manually, but the level of work involved to keep this up to date would be too time consuming, and I would love to know if there is a formula out there to make life a little easier!

All the best,

Kilian

[dogberry]

Hi

Have you tried recording a macro of what you are doing, therafter just call the macro.

Chris

[kilianfoy]

Hi Chris,

I have recorded a macro, but when the employee structure changes it reassigns information to the incorrect employee. Again, I may be recording the macro incorrectly! What would be the best process to record a macro in this instance?

[Richard Buttrey]

Hi,

On the face of it there seems no reason why you can't although remember there's no fuzzy logic available in standard Excel. You could use a helper column in system A to slice out the "Bloggs" part of "Joe Bloggs". So suppose "Bloggs' is in helper cell A1, use a formula like

=Match(A1&"*",systemB_lookup_column,False)

Then if necessary have a macro create and copy this formula down a list range if the range varies.

[oeldere]

You can start to put your excel file, without confidentional information on the forum.

If you also add the desired result, we can look for an solution.

After that it can be record with the macro-recorder (or someone makes an written code).

[kilianfoy]

Ok, so say that A2 is my search parameter and H2 is what I wish to copy into cell B2. This would normally be on other worksheets, but for the moment I'd be happy with it working on one! I want to transfer the information in H2 to B2 by using A2 as my search function. Also, if the information in A2 were to change, I would want it to find the appropriate information from a list of employee names and update correctly.

System A Name System B Name DOB Address Post Code System B Name
Joe Bloggs xx xx xx Bloggs, Joe


@Richard, the formula returned a #Value error, would the solution above have A1 only containing the surname Bloggs?