+ Reply to Thread
Results 1 to 12 of 12

Return last populated cell from column to the left until next populated cell occurs

  1. #1
    Registered User
    Join Date
    07-30-2007
    Location
    United Kingdom
    Posts
    8

    Question Return last populated cell from column to the left until next populated cell occurs

    I hope that title makes sense! Even reading it back it sounds a bit overly cryptic.

    I have a column (K) with numbers in there every now and again and I would like column L to populate with the value that most recently occurred.

    An example will hopefully explain much better :

    This is what I have:
    before.jpg

    This is what I'd like:
    after.jpg

    Just in case it makes a difference the value in column K is the result of an IF formula to return non blanks from another column.

    Thanks in advance for your help
    Attached Images Attached Images
    Last edited by abx; 01-23-2017 at 09:47 AM. Reason: Changed PNG to JPG

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return last populated cell from column to the left until next populated cell occurs

    Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return last populated cell from column to the left until next populated cell occurs

    Maybe like this?

    =IF(K1=0,OFFSET(L1,-1,0),K1)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    07-30-2007
    Location
    United Kingdom
    Posts
    8

    Re: Return last populated cell from column to the left until next populated cell occurs

    Thanks for the tip. Adjusted PNG to JPG and re-uploaded and edited OP.

  5. #5
    Registered User
    Join Date
    07-30-2007
    Location
    United Kingdom
    Posts
    8

    Re: Return last populated cell from column to the left until next populated cell occurs

    Quote Originally Posted by AliGW View Post
    Maybe like this?

    =IF(K1=0,OFFSET(L1,-1,0),K1)
    This worked perfectly when adjusted to =IF(K1="",OFFSET(L1,-1,0),K1)
    (IE: Swapped =0 to ="")

    Thank you!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return last populated cell from column to the left until next populated cell occurs

    You're welcome.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return last populated cell from column to the left until next populated cell occurs

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return last populated cell from column to the left until next populated cell occurs

    Try this...

    Data Range
    K
    L
    1
    2
    47455
    47455
    3
    47455
    4
    47455
    5
    47455
    6
    47455
    7
    47466
    47466
    8
    47466
    9
    47468
    47468
    10
    47471
    47471


    This formula entered in L2 and copied down:

    =LOOKUP(1E100,K$1:K2)

  9. #9
    Registered User
    Join Date
    07-30-2007
    Location
    United Kingdom
    Posts
    8

    Re: Return last populated cell from column to the left until next populated cell occurs

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Data Range
    K
    L
    1
    2
    47455
    47455
    3
    47455
    4
    47455
    5
    47455
    6
    47455
    7
    47466
    47466
    8
    47466
    9
    47468
    47468
    10
    47471
    47471


    This formula entered in L2 and copied down:

    =LOOKUP(1E100,K$1:K2)
    This solution also works nicely. Rep added to both answers and thread marked as Solved. Thank you all.

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Return last populated cell from column to the left until next populated cell occurs

    You can also do this
    Enter in L2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v K L
    1
    2 65423 65423
    3 65423
    4 65423
    5 987654 987654
    6 12321 12321
    7 12321
    8 12321
    9 123231 123231
    Last edited by AlKey; 01-23-2017 at 10:24 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return last populated cell from column to the left until next populated cell occurs

    You're welcome. Thanks for the feedback!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return last populated cell from column to the left until next populated cell occurs

    Just for completeness' sake, I opted for the OFFSET version because you can start it in row 1.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 09-30-2016, 10:48 AM
  2. Replies: 11
    Last Post: 04-11-2013, 10:56 AM
  3. Replies: 17
    Last Post: 01-12-2013, 11:31 AM
  4. Replies: 2
    Last Post: 01-09-2011, 01:24 PM
  5. Replies: 8
    Last Post: 02-10-2010, 05:34 PM
  6. Finding the last populated cell in a column array
    By JosephJames in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-18-2009, 12:42 PM
  7. Value of last populated cell in column
    By eintracht in forum Excel General
    Replies: 1
    Last Post: 03-05-2009, 04:19 AM

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