How to line up the last cell in each row?

[Nathan Benedict]

I have some data for which I am only interested in the final column from
each row. The problem is that each row is a different length, so that row 1
may be 10 columns wide, row 2 15 columns, etc. How can I get the last
element from each row to line up in the same column so that I can sort, sum,
etc.?

 

[Boxman]

Here is one solution:

Insert a new column at A1 so you now have shifted all of your data to
the right. Assuming that Row 1 has headers your first data record will
be in row 2.

Type this formula into A2

=OFFSET(B2:IV2,0,COUNTA(B2:IV2)-1,1,1)

Drag copy down to the end of your data.

Column A now contains a copy of the last cell in each row regardless of
how many active cells are in the row. Note you can not have any blank
cells prior to the last cell in the row. If you do, fill them with
something.