Extracting subdate for a group for every record separately

[smg]

Hello,

This has been a problem for me for a while but so far I've managed to deal with it manually.

I've a got the following table:


[TABLE="width: 772"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Planned Delivery Date : 01-08-2012
[/TD]
[/TR]
[TR]
[TD]x1[/TD]
[TD]y1
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Planned Delivery Date : 02-08-2012
[/TD]
[/TR]
[TR]
[TD]x1[/TD]
[TD]y1[/TD]
[/TR]
[TR]
[TD]x2[/TD]
[TD]y2[/TD]
[/TR]
[TR]
[TD]x3[/TD]
[TD]y3[/TD]
[/TR]
[TR]
[TD]x4[/TD]
[TD]y4[/TD]
[/TR]
[TR]
[TD]x5[/TD]
[TD]y5
[/TD]
[/TR]
</tbody>[/TABLE]

As you can see Planned Delivery Date is like a subtitle which is valid for all below records until the next date appears. The number of dependent records is not constant.
I'd like to extract Planned Delivery Date for each record separately so the date appear in, for example, column C in every line (except for blanks in A).
I've got another sheet which, with help of a macro, present the data in the way I want. My idea so far is that I could used formula FIND("Planned Delivery Date") and if the value and if it's >0 then do the vlookup for every filled cell in Column A until another FIND >0.
I don't mind using a standard formula as I have another spreadsheet in the same workbook which pulls out the data from the sheet above. The problem is, I don't know how to get there.

Knowing how to do what I described above would be very helpful not only for this particular task but for many others.


Cheers,
Slawek

 

[Rick Rothstein]

Assuming the first "Planned Delivery Date" data line is on Row 1, put this formula in C2 and copy it down as needed...

=IF(OR(B2="",LEFT(B2,21)="Planned Delivery Date"),"",IF(LEFT(B1,21)="Planned Delivery Date",TRIM(RIGHT(SUBSTITUTE(B1," ",REPT(" ",99)),99)),C1))

 

[smg]

Hi Rick,

First of all thank you for your reply.

I inserted the formula but it gives blank cells.


Do you have any other solution to this issue?

Regards,
Slawek

 

[smg]

Hi again Rick,

I removed the spaces from " " (SUBSTITUTE formula) and it works perfectly.


Thanks very much!

Slawek