Report data needs to conform to db format

[Jim May]

Each month I receive a report, say 200 lines (rows) 8 columns
The information is grouped and separated as follows:

Date Name Code Div Cost
05/15/04 Jim Bc West 20.00
05/20/04 John CB East 15.00
06/15/04 Judy BC South 35.00
Grp1 70.00

The sample above is a block of 3 the next block might be
a block of 1, then 3rd a block of 5 or 7 and so on
I'm currently manually Inserting a new column A:A
and entering into A2:A4 the "Grp1" which is in Cell E5 after the column
Insert
Can I enter a formula in New ColA to bring back the Cell Value
or do I need to use VBA? Either way I don't know how to acheive.
Can someone help?

 

[jeff]

Hi, Jim,

After I inserted a new Col A, I ran this macro which
seemed to do what you're asking.

jeff

Sub findSetGrp()
Dim lastrow As Long
lastErow = ActiveSheet.Cells(Rows.Count, "f").End
(xlUp).Row
lastrow = 2
With Worksheets(1).Range("f2:f" & lastErow)
Set c = .Find("Grp", LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
For RowIndex = lastrow To c.Row - 1
Range("a" & RowIndex).Value = c.Value
Next RowIndex
lastrow = c.Row + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <>
firstAddress
End If
End With

End Sub

 

[Frank Stone]

If all you are trying to do is move the data from old
column d to new column A then the formula =E2 entered in
Cell A2 should do that.

 

[Debra Dalgleish]

To do this with a formula, create a blank column A
In cell A2, enter the formula:
=IF(AND(B2="",F2=""),"",IF(AND(B2="",F2<>""),F2,A3))

Copy down to the last row of data.

You may want to then copy Column A, and paste Special, as Values.

 

[Jim May]

Debra: Thanks for the reply; I used your suggestion. It works great...
appreciate your assistance..
Jim May

 

[Debra Dalgleish]

You're welcome. Thanks for letting me know that it worked.