Extracting subdate for a group for every record separately

smg

New Member
Joined
Aug 22, 2012
Messages
14
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:


COLUMN ACOLUMN B
Planned Delivery Date : 01-08-2012
x1y1
Planned Delivery Date : 02-08-2012
x1y1
x2y2
x3y3
x4y4
x5y5

<colgroup><col><col></colgroup><tbody>
</tbody>

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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

New Member
Joined
Aug 22, 2012
Messages
14
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

New Member
Joined
Aug 22, 2012
Messages
14
Hi again Rick,

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


Thanks very much!

Slawek
 

Forum statistics

Threads
1,082,610
Messages
5,366,598
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top