1

Hello, we are dealing with 2 excel worksheets. Sheet one has a list of all employees and no dependent information. Sheet two has a list of employees’ dependent information. The way the dependent information is structured, there is a new row for each dependent and the employee information is the same in each row corresponding to his/her dependents. Below is a simple example of what I am talking about.

What we want to do is add each dependent’s name in new columns next to the employee they correspond to. Using the below table as an example, on John Doe’s row in Sheet one there should be columns for all 3 of his dependents (Jane, Bob and Joe).

I hope I am clear with what I am trying to accomplish, if not I can clarify additionally.



Employee First Name

Employee Last Name

Dependent First Name

Dependent Last Name


John

Doe

Jane

Doe


John

Doe

Bob

Doe


John

Doe

Joe

Doe


David

Smith

Dylan

Smith


Becky

Rush

Doria

Rush


Becky

Rush

Mac

Rush
3 Spice ups
2

I guess my first question is, why not just add a new column into one of the sheets and copy paste in the name?

Or is the problem that they have both first name / last name in 1 cell? If so you can just do a =LEFT =RIGHT of a cell to extract the individual fname, lname?

3

I think I understand what you want to do, and I don’t think it will be possible using standard Excel functions. You could do it using a custom VBA function (not difficult if you know VBA).

Is this a one-off task or something that will be repeated? Approx. how many records are involved?

Your data is actually structured nicely for a relational database. It would be much easier to achieve using a database and your Excel worksheets could easily be linked to an Access DB. Then a simple query will give you the format you are looking for and it could be exported back to Excel if required.