I don't understand the request. The sample is not clear. Can you try again? Maybe post a small sample workbook.
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
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
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:
replacing "Sheet1" for actual sheetname.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
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.
No better, just wanted to show how to dynamically determine the column count - seemed worth it for that.
Always is, make it far less visually traumatic, and can dramatically improve performance (I know you know that NBVC, for the OP's edification )
Bookmarks