Excel Formula

[camster39]

Column A contains Soc Sec No
Column B is Employee Last Name
Column C is Employee Middle Name
Column D is Employee First Name
Column E is dependent Last Name
Column F is dependent First Name

Sorted the spreadsheet by column A – if an employee has more than 1 dependent there is a repeat of the SSN

Example:

444-55-6666 Smith John Smith Ray Child Family Cov
444-55-6666 Smith John Smith Karen Child Family Cov
444-55-6666 Smith John Smith Debbie Spouse Family Cov

Is there a way for me to create a NEW row if there is a match in column A. In other words, turn the above into this:

444-55-6666 Smith John
444-55-6666 Smith Ray Child Family Cov.
444-55-6666 Smith Karen Child Family Cov.
444-55-6666 Smith Debbie Spouse Family Cov.

Since the dependents are tied to the employee, I would keep column A the same (SSN of the employee). The remaining data on a particular row should just be moved down and inserted into another row.

Hopefully I'm explaining this OK. Is this something that can be done with a formula?

Using Excel 2000.

Thank you

 

[droldog]

I am not sure this is exactly what you are looking for, but you can do something like this:

Create a second worksheet (Sheet2)with Sheet1 being the original data:

Put you headings in the first row as it on sheet1.

In cell A2 put "=Sheet1!A2" (quotes not included)

then, based on your column description above put:

in cell B2 "IF($A2=$A1,$E2,$B2)" (quotes not included)

in cell C2 "IF($A2=$A1,$F2,$C2)" (quotes not included)

Assuming that your list is sorted and the data is arranged as you saw above, it will put the employees name in the first entry and then use the dependents name in each additional entry. This will handle the part about splitting in off.

Adding the first line would have to be done in the original first. It would be easiest to just insert a row and copy the info from first dependent. It doesn't matter that you will have that data twice since it will pick out the employee name the first time and the dependent name the second time. I am guessing that that could be done with a macro and some of the macro programmers may be able to suggest how to do that part.

What I have included above with just give the social with the employee name on the first line and then the social with the dependent name on each additional line as you have requested.

You would then just drag the fomulas on the scond page down through all the rows.