Find last value in row and move to column

Fergus

New member
Joined
Feb 4, 2015
Messages
6
Reaction score
0
Points
0
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.
 
I don't understand the request. The sample is not clear. Can you try again? Maybe post a small sample workbook.
 
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

ABCDEFGH
1Text Value 1Text Value 2Text Value 3Text Value 4
2Text Value 1Text Value 2Text Value 3
3Text Value 1Text Value 2
4
Text Value 1Text Value 2Text Value 3Text Value 4
5Text Value 1Text Value 2Text Value 3


Hopeful Result from formula


ABCDEFGH
1Text Value 1Text Value 2Text Value 3Text Value 4
2Text Value 1Text Value 2Text Value 3
3Text Value 1Text Value 2Text Value 2
4
Text Value 1Text Value 2Text Value 3Text Value 4
5Text Value 1Text Value 2Text 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
 
Apologies, the in B3 of the 2nd table, I should have deleted "Text Value 2" as it now appears in H... small oversight
 
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.
 
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
 
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.
 
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
 
If it's coming from Bob, it's a better alternative ;)

I forgot the Application.ScreenUpdating... always should be included.
 
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.

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 :))
 
Hi guys,

really apologize for the late thanks on your help above! Cheers, really helped me out of a tough one.

Thanks again
 
Thanks so much for all the help guys! This has been an absolute time saver for me

Thanks again,
Ferg
 
Back
Top