Heading Lookup inside Column Data

[GlenKenn]

Hello,
I'm trying to manipulate the output from a program in order to make it usable in a pivot table. Currently the output will display both sub-headings and data in the same column, and I need a formula that can search up the column to find the sub-heading for that data then output the data to a new column to the right of the data.

the current output looks like this:

PROJECT ProjectA
RESOURCE john Smith
RESOURCE Peter Jones
RESOURCE Rupert Brown
PROJECT ProjectB
RESOURCE Ralph Simpson

What I am hoping for is a formula that can lookup Column A to find the next instance of "PROJECT", then place the Value in Column B for that row beside the resource name, as below:

PROJECT ProjectA
RESOURCE john Smith ProjectA
RESOURCE Peter Jones ProjectA
RESOURCE Rupert Brown ProjectA
PROJECT ProjectB
RESOURCE Ralph Simpson ProjectB

Unfortunately there is no consistency in the output from a month to month basis, so I can't use fixed cells for the sub-heading.

Any assistance that anyone can provide would be greatly appreciated. I've been searching google, but without luck so far.

Thanks

Glen

[rylo]

Hi

Are PROJECT and ProjectA in the same column (eg A) or are the 2 parts separated and are in say, columns A and B?

rylo

[GlenKenn]

Hi,
Sorry the formatting was lost when I posted the question, they are in separate columns.
So, "PROJECT" would be in column A and "ProjectA" would be in column B.

"ProjectA" would be the name of the project.

"RESOURCE" would be in Column A, and "John Smith" etc, would be in Column B.

Thanks

Glen

[Haseeb Avarakkan]

Hello Glenn,

Assuming data will be in A1:B6, so in C2,

=LOOKUP(9E+300,SEARCH("Project",B$1:B2),B$1:B2)

Then copy down.

If this is not your requirement, please attach a sample file.

To attach a file, When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[rylo]

Hi

Assuming that your example data is in the range A1:B6 then
C1: =B1
C2: =IF(A2="PROJECT",B2,C1)

Copy from C2 down to C6

HTH

rylo

[GlenKenn]

Thanks Haseeb,

That works beautifully. Can I ask what the value "9E+300" does?