Seperate Rows

[camster39]

Column A - Employee SSN
Column B - Employee First Name
Column C - Employee Last Name
Column D - Benefit Plan Type

If the value in Column D is anything other than "Employee" then there will be a value in columns E,F & G, otherwise there will be no values in E,F & G.

Column E - Dependent First Name
Column F - Dependent Last Name
Column G - Dependent SSN

I'm looking to create some type of automated process where I can look at Column D and 'insert' the dependent information directly below the employee information. For example a file may look like this....

123456789,Jon Doe,Employee + Family,Jane Doe,111223333
123456789,Jon Doe,Employee + Family,Fred Doe,222334444
123456789,Jon Doe,Employee + Family,Ann Doe,333445555

Whereas I need the file to look like this

123456789,Jon Doe,Employee + Family
123456789,Jane Doe,Employee + Family,111223333
123456789,Fred Doe,Employee + Family, 222334444
123456789,Ann Doe,Employee + Family, 333445555

Is there any type of macro, advanced filter, VBA etc... that would work in this scenario?

Using Excel 2000.

Thank you.

 

[kjv1611]

I'm sure you can set it up in VBA. The determining factor will be just how important is it to you, and do you want to delve into the VBA.

If you do decide to go with VBA for it, and run into any problems you might want to post those questinos in the forum:

VBA Visual Basic for Applications (Microsoft) Forum, forum707

 

[DirkStruan]

Try something along the lines of the following (note that a couple of columns get deleted)

Sub rearrange()
Dim x As Long
x = 2
Do While Cells(x, 1) <> ""
    If Cells(x, 1) <> Cells(x + 1, 1) Then
        z = x
        If Not (IsEmpty(Cells(x, 5))) Then
            Cells(x + 1, 1).EntireRow.Insert
            z = z + 1
        End If
        Cells(z, 1) = Cells(x, 1)
        Cells(z, 5) = Cells(x, 2)
        Cells(z, 6) = Cells(x, 3)
        x = x + 1
    End If
    x = x + 1
Loop
Columns("E:F").Cut Columns("B:C")
Columns("E:F").EntireColumn.Delete Shift:=xlToLeft
End Sub

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[Gavona]

how about you:
Make a copy of the worksheet
Delete column B + C (containing employee name)
Move column E + F [dependant name] to become column B + C

Filter D for "employee"
highlight visible cells and Edit,Delete,EntireRow
Data,Filter,Showall

Copy and paste to the bottom of your original data.

Sort on Column A

To automate:
Record yourself doing this and post the code in forum707 if you need help to refine it. For example the code you record will probably specify the range to be copied. You may want to .CurrentRegion to select your entire database as it grows. (look it up in help)



Gavin