Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Find last value in row and move to column

  1. #1

    Find last value in row and move to column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    I was wondering if anyone could me with a formula that can do the following

    1. Find last cell with a value in row
    2. Move last cell with value to desired column

    And this formula or VBA do this for all 15000+ rows.

    The data looks like the following little illustration (each number represents a column)

    Row 1: 12345
    Row 2: 1234567
    Row 3: 123456789

    And I need it do to this

    Row 1: 1234 5
    Row 2: 123456 7
    Row 3: 12345789 9

    Numbers 5, 7, 9 would be in the same column

    Thanks so much in advance

    Any help would be greatly appreciated.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    I don't understand the request. The sample is not clear. Can you try again? Maybe post a small sample workbook.


  3. #3
    No problems, Thanks for your interest - I have attached a small sample workbook

    I think I may have found close to what I am looking for - =OFFSET(A1,0,MATCH(MAX(A1:E1)+1,A1:E1,1)-1)

    However I need this to return text values, as this formula only returns number values.

    Current

    A B C D E F G H
    1 Text Value 1 Text Value 2 Text Value 3 Text Value 4
    2 Text Value 1 Text Value 2 Text Value 3
    3 Text Value 1 Text Value 2
    4
    Text Value 1 Text Value 2 Text Value 3 Text Value 4
    5 Text Value 1 Text Value 2 Text Value 3


    Hopeful Result from formula


    A B C D E F G H
    1 Text Value 1 Text Value 2 Text Value 3 Text Value 4
    2 Text Value 1 Text Value 2 Text Value 3
    3 Text Value 1 Text Value 2 Text Value 2
    4
    Text Value 1 Text Value 2 Text Value 3 Text Value 4
    5 Text Value 1 Text Value 2 Text Value 3

    So the last value of each row will now be column H in the same row

    Hope that is okay, thanks again for having a look. I know the formula i have above is so close, I just need it to work with words, not numbers

    Cheers

  4. #4
    Apologies, the in B3 of the 2nd table, I should have deleted "Text Value 2" as it now appears in H... small oversight

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Note that a formula will not be able to physically transfer the row's last values as you have shown. A formula will only be able to populate column H with the last value, but the last value will still remain in the original location.

    You will need VBA to actually remove it.

    In the meantime, you can use a formula in column H like:

    =LOOKUP(2,1/(A1:G1<>""),A1:G1)

    copied down.


  6. #6
    Thankyou kind sir.. this worked and very efficient... thank you mate.

    If by chance, and only if not asking too much, could provide some sort of VBA, that would be great - the closest I got is below with VBA, but this only navigated to the last populated cell, and I didn't get as far as moving then deleting from original cell

    As you can tell... new to this.


    Sub LastCellBeforeBlankInRow()Range("A1").End(xlToRight).SelectEnd Sub

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    Code:
    Sub LastCellBeforeBlankInRow()
    Dim i As Long
    With Sheets("Sheet1")
        For i = 1 To .UsedRange.Rows.Count
            .Cells(i, .Columns.Count).End(xlToLeft).Cut .Range("H" & i)
        Next i
    End With
    
    End Sub
    replacing "Sheet1" for actual sheetname.


  8. #8
    An alternative

    Code:
    Public Sub MoveAlong()
    Dim lastcol As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            For i = 1 To .Range("A1").End(xlDown).Row
            
                lastcol = .Cells(i, "A").End(xlToRight).Column
                .Cells(i, lastcol).Resize(, 8 - lastcol).Insert Shift:=xlToRight
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    If it's coming from Bob, it's a better alternative

    I forgot the Application.ScreenUpdating... always should be included.


  10. #10
    Quote Originally Posted by NBVC View Post
    If it's coming from Bob, it's a better alternative
    No better, just wanted to show how to dynamically determine the column count - seemed worth it for that.

    Quote Originally Posted by NBVC View Post
    I forgot the Application.ScreenUpdating... always should be included.
    Always is, make it far less visually traumatic, and can dramatically improve performance (I know you know that NBVC, for the OP's edification )

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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