Cell Change Help

[mtang21]

Hi everyone,

I have been racking my brain on how to make this work exactly. I need a macro or a formula atleast to make my data flow into a report.

Currently it looks like this:
 
Fund 1 09/12/2008 Dodge&Cox
PIMCO
American FUNDS
Janus
Vanguard
 
Fund 2 09/12/2008 Calamos
Goldman Sachs
State Street 

What i need is this report to look like this:
 
Fund 1 09/12/2008 Dodge&Cox
Fund 1 PIMCO
Fund 1 American FUNDS
Fund 1 Janus
Fund 1 Vanguard
 
Fund 2 09/12/2008 Calamos
Fund 2 Goldman Sachs
Fund 2 State Street 

I need to find a way to copy the top cell of each section to filter down to all its components but when the cell changes, i need it to stop copying that section and start copying the next section. I don't want to copy and paste the entire time. Any help would be tremendous! Thanks all!

[Kenneth Hobs]

Is each line in one column?

[mtang21]

fund 1 is 1 column...then date is another...then mutual fund is the last

[Kenneth Hobs]

[VBA]Sub SetFunds()
Dim fundStr As String
Dim firstCell As Range, nextCell As Range, curCell As Range, fCell As Range
Dim rNum As Long

rNum = -1
Set firstCell = Range("A1")
Set curCell = firstCell.Offset(0, 0)
Set nextCell = curCell.Offset(1, 0)
Do Until nextCell.Value = "" And curCell.Value = ""
If curCell.Address = firstCell.Address Then
Set fCell = curCell
Else: If curCell.Offset(-1, 0) = "" Then Set fCell = curCell
End If
If curCell.Address <> fCell.Address And curCell.Value <> "" Then
curCell.Offset(0, 1).Value = curCell.Value
curCell.Value = fCell.Value
End If
rNum = rNum + 1
Set curCell = firstCell.Offset(rNum, 0)
Set nextCell = curCell.Offset(1, 0)
Loop
End Sub[/VBA]

[mdmackillop]

[VBA]Sub SetFunds2()
Dim rng As Range, cel As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng
If Left(cel, 4) <> "Fund" And cel <> "" Then
cel.Insert Shift:=xlToRight
cel.Offset(, -1).FillDown
End If
Next
End Sub
[/VBA]