How do I delete cells to the left of my latest value?

For example- In the attached image I want to have only the value in column E for row 3, only the value in column D for row 5 & 6, only the value in column C for row 8.

Any help is greatly appreciated!

CodePudding user response：

If wanting to display the last value of each row in Column F, then input this formula into column F:

=OFFSET(A2,0,MATCH(MAX(A2:E2) 1,A2:E2,1)-1)

I'm sure there are several ways to accomplish this, and this is one such method.

----------------EDIT -----------------

Based on your comment, looking for the column number of the latest value, then the easiest method is simply using a COUNTA formula, provided you have no gaps in your data.

=COUNTA(A2:E2)

---------------EDIT 2 ----------------

If you indeed have gaps, then you can use this formula to obtain the last column with data:

=AGGREGATE(14,6,COLUMN(A2:E2)/((A2:E2<>"")),1)