Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

Application.ScreenUpdating = False

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
If InStr(.Cells(i, "B").Value, "Total") 0 Then
.Rows(i).Delete
Else
If .Cells(i, "B").Value = "Fund2" Then
.Cells(i, "D").Cut .Cells(i, "E")
ElseIf .Cells(i, "B").Value = "Fund3" Then
.Cells(i, "D").Cut .Cells(i, "F")
End If
If .Cells(i, "A").Value = .Cells(i - 1, "A").Value And _
.Cells(i, "C").Value = .Cells(i - 1, "C").Value Then
If .Cells(i, "D").Value < "" Then .Cells(i, "D").Cut
Cells(i - 1, "D")
If .Cells(i, "E").Value < "" Then .Cells(i, "E").Cut
Cells(i - 1, "E")
If .Cells(i, "F").Value < "" Then .Cells(i, "F").Cut
Cells(i - 1, "F")
.Rows(i).Delete
Else
.Cells(i, "C").Resize(1, 4).Cut .Cells(i, "B")
End If
End If
Next i

.Range("B1").Value = ""
.Range("C1").Value = "Fund1"
.Range("D1").Value = "Fund2"
.Range("E1").Value = "Fund3"

End With

Application.ScreenUpdating = True


End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"ToddEZ" wrote in message
...
Please help! I am trying to write a macro that will change data that
looks
like this:

SSN Fund Source Ending Balance
xxx-xx-xxxx Fund1 Profit Sharing 10000
xxx-xx-xxxx Total Fund1 10000
xxx-xx-xxxx Grand Total 10000
yyy-yy-yyyy Fund1 Profit Sharing 5000
yyy-yy-yyyy Fund2 Profit Sharing 5000
yyy-yy-yyyy Fund3 Company Match 3000
yyy-yy-yyyy Fund3 Deferred Salary 6000
yyy-yy-yyyy Total Fund1 5000
yyy-yy-yyyy Total Fund2 5000
yyy-yy-yyyy Total Fund3 9000
yyy-yy-yyyy Grand Total 19000

Into something that looks like this:
SSN Source Fund1 Fund2 Fund3
xxx-xx-xxxx Profit Sharing 10000
yyy-yy-yyyy Profit Sharing 5000 5000
yyy-yy-yyyy Company Match 3000
yyy-yy-yyyy Deferred Salary 6000

Any ideas?