+ Reply to Thread
Results 1 to 6 of 6

Heading Lookup inside Column Data

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Heading Lookup inside Column Data

    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

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Heading Lookup inside Column Data

    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

  3. #3
    Registered User
    Join Date
    10-08-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Heading Lookup inside Column Data

    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

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,199

    Re: Heading Lookup inside Column Data

    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
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Heading Lookup inside Column Data

    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

  6. #6
    Registered User
    Join Date
    10-08-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Heading Lookup inside Column Data

    Thanks Haseeb,

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1