Copy Value from one Cell, to Cells below but only until it finds a substring

[Baaleos]

Ok, hypothetical situation....
Which is less hypothetical, and more real....

We have a report script which writes report details out to an excel document,
this is all very well and good, but we need to migrate to mySQL now, and the format of the excel is incompatible.


The way the report entries are currently labelled by date, is as follows.

Row 1, Cell A
ReportDate:01/09/2010

Then
Row 2 Cell A
Content associated with the above date

Row 3 Cell A
Content associated with the above date

Then it might get down to row 35 and have a new date and the records contained on the 35 rows after it, will be associated with that date.




Anyway, I was wondering if any one here has a wonderfully complicated formulae, which can iterate through each Row in Column A, and if it finds a value which has the substring
"LabReport_" or something like that, then it will copy that value, into the Rows which come below it, but in Column J or something.... just so we can get the date along side each record.

The querk however, is that we need the script or formulae to detect new dates in Column A, and then put these new values into the Next rows, in the appropriate column.


I have over 6000 rows of data to do this on.

:-(

[rylo]

Hi

Example file time, with data that represents your real situation (but nothing sensitive), and an example of how the output based on that sample data should look.

rylo

[Baaleos]

Solved this via using QTP

dim rowval,dateval
for i = 1 to datatable.GetRowCount
Datatable.setcurrentrow(i)
rowval = datatable.value("A","Global")
if instr(rowval,"LabReport_") then
    dateval = rowval

else
    datatable.value("U","global") = dateval
    
end if
next

Basically, it loops through the rows, and conditionally puts the contents of dateval into column U on that row. dateval is always equal to the previous instance where it encountered a LabReport header in column A.
Since dateval updates each time it encounters another header, it then updates the appropriate row, with the appropriate values.