+ Reply to Thread
Results 1 to 6 of 6

Return Last Text value in Same Row in Excel

  1. #1
    Registered User
    Join Date
    11-01-2011
    Location
    Rhode Island, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Return Last Text value in Same Row in Excel

    I have a worksheet in Excel 2010 that has the following columns:
    ITEM (text)
    NOTES (text)
    LAST REVIEW RESULT **This is the one I am having trouble with**
    WEEK 1 (text returned from a vlookup to another sheet)
    WEEK 2 (text returned from a vlookup to another sheet)
    WEEK 3 (text returned from a vlookup to another sheet)
    WEEK 4 (text returned from a vlookup to another sheet)
    ETC
    ETC
    ETC

    In the "WEEK" columns, I have an alpha numeric field (text in Excel) with a Job ID (random values-no order). A value will only appear in the column if there is a JOB ID for that ITEM for that WEEK. (So not all WEEK columns will have values).

    I want the LAST REVIEW RESULT column to return the text value from the last cell with a value in that row. So if WEEK 10 in row 4 is the last week to show any entry in row 4, I want the LAST REVIEW RESULT (column C) to return the value from WEEK10 in column 4.

    I can't for the life of me figure out how to make this work! Can anyone help? Thank you!
    Last edited by mdispbee; 11-01-2011 at 01:54 PM. Reason: Solved

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Return Last Text value in Same Row in Excel

    Try something like:

    =LOOKUP(2,1/(D2:X2<>""),D2:X2)

    copied down

    where D2:X2 is the range of the first set of values to process...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Return Last Text value in Same Row in Excel

    And another way?
    In C2
    Please Login or Register  to view this content.
    Hope this helps
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Registered User
    Join Date
    11-01-2011
    Location
    Rhode Island, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Return Last Text value in Same Row in Excel

    Thank you, both! Turns out that part of my problem was that someone had saved some of the job codes as dates instead so I was getting some funny results. Converted to text and it's perfect! Thank you!

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Return Last Text value in Same Row in Excel

    I would have suggested something similar, Marcol, but if I am correct in assuming the OP has VLOOKUPs returning results... and I assume that if there is no match the OP would return a blank.... if that is true, then the MATCH(REPT("z",255)... may not work.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Return Last Text value in Same Row in Excel

    Good point NBVC I missed this even though it was written down 4 times!!!
    ...text returned from a vlookup to another sheet...
    Time I was out of here, my dinner might be going flat! ...

    Let's hope the VLOOKUP doesn't return a number!
    Last edited by Marcol; 11-01-2011 at 02:26 PM.

+ 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