Ask Your Question
0

Select last cells with a value for a given row

asked 2019-06-25 13:22:17 +0100

sr1pulpo gravatar image

Hello all, My very first post. happy days.

I am trying to select the last 5 cells that contain a value (number) from a row (very long row with lots of gaps).

Ideally I would like to get the last 5 values for a number of rows from sheet 1 and have those end up in sheet 2 (a lovely summary with the last 5 results).

I can update manually but if I could automate this so that values are sent to sheet 2 as I update sheet 1 that would be fantastic.

thank you.

edit retag flag offensive close merge delete

Comments

What would be "the last 5 values for a number of rows"; the last 5 numbers across an arbitrary number of rows, or the last 5 values of one row for an arbitrary number of rows, or ...?

erAck gravatar imageerAck ( 2019-06-25 17:25:49 +0100 )edit

Hi. Apologies if this was not clear.

I want to get the value for the last 5 cells in any given row.

By that I mean I want to extract whatever is written in the last 5 cells of a row number or characters or mixed.

The problem is I have gaps in the rows. That is some cells are blank. So I need to tell the spreadsheet to ignore empty cells in the row and only get the last 5 cells that actually contain something.

Hope this is a bit clearer.

thanks everyone

sr1pulpo gravatar imagesr1pulpo ( 2019-06-25 18:21:47 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-06-25 17:16:05 +0100

erAck gravatar image

updated 2019-06-25 20:39:08 +0100

To lookup the last value within one given row:

=LOOKUP(2;1/(Sheet1.42:42<>"");Sheet1.42:42)

which retuns the last value in row 42 on Sheet1.

If you have mixed text and numeric values within a row and want the last numeric value then

=LOOKUP(2;1/ISNUMBER(Sheet1.42:42);Sheet1.42:42)

All this needs at least LibreOffice 6.1 to work.

Update: 2019-06-25T20:38+02:00

To obtain the last 5 numeric values of one row use

=LOOKUP(LARGE(IF(ISNUMBER(Sheet1.42:42);COLUMN(Sheet1.42:42));{5,4,3,2,1});COLUMN(Sheet1.42:42);Sheet1.42:42)

entered as array formula (close with Shift+Ctrl+Enter instead of just Enter), it will then spread over 5 columns. Note that within the inline array {5,4,3,2,1} the array column separator (here , comma) needs to be used, configured under Tools -> Options -> Calc -> Formula, which depends on the locale. The formula returns #VALUE! error in all array elements if less than 5 numeric values are available.

edit flag offensive delete link more

Comments

can this be adapted to get a range? last 5 cells for a row?

sr1pulpo gravatar imagesr1pulpo ( 2019-06-25 18:26:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-25 13:22:17 +0100

Seen: 156 times

Last updated: Jun 25