CODEWITHSUNDEEP
excelopenoffice-calc
Mike
Mike

Reputation: 211

Find last column with data and return the header of that column

I'm working in Apache OpenOffice calc (v4.1.13) and would like to search a row, find the last row with a non-empty cell and return the column header for that row. Note that the cells contain text and that some cells in the row are empty.

In this post they used =LOOKUP(2,1/(H228:S228<>""), H1:S1) to return the column header of the last row with data. I first changed the commas to semicolons to work with OpenOffice, then changed the row values to match my ranges to get the following: =LOOKUP(2;1/(F4:I4<>"");F1:I1) but I get a #DIV/0 error.

The #DIV/0 error goes away and the function works if I put dummy data in what were blank cells in F4:I4.

From what I understand, in Excel this formula will work if some of the cells in the row are empty. This seems not to be the case in OpenOffice Calc, as I get a #DIV/0 error. How do I make this work in Calc?

Upvotes: 2

Views: 1556

Answers (2)

Mike
Mike

Reputation: 211

Jim K got very close, I've adapted his suggestion and post it here. The following formula finds the last column with text in row X (in this case 9) and returns row Y of that same column (in this case #1, column header). I can confirm the following:

  • Works with Apache OpenOffice
  • Works when there are empty cells in the row

=LOOKUP("ZZZ";F9:ZZ9;F1:ZZ1)

Many thanks to those that helped with this!

Upvotes: 1

Jim K
Jim K

Reputation: 13790

Adapted from https://ask.libreoffice.org/t/return-the-last-value-in-a-column/53646:

=LOOKUP(2;1/ISNUMBER(H228:S228);H228:S228)

Here is the explanation from that post:

LOOKUP() forces its arguments into array mode. In this ISNUMBER(...) returns an array of 1 and 0 values, then 1/ISNUMBER(...) produces an array of 1 and #DIV/0 error values. LOOKUP() has the behaviour of silently ignoring error values in such array, thus looking up the value 2 returns the position of the last 1 value. Looking up 1 instead in this case works as well, but internally more elements are compared to find the last matching value, whereas for value 2 the search does a complete binary search to rule out blocks of data, which is faster.

EDIT:

This works for text as well as numbers.

=LOOKUP(2;1/NOT(ISBLANK(H228:S228));H228:S228)

EDIT 2:

Here is a solution for AOO, adapted from https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=66962:

=LOOKUP("ZZZ";H228:S228)

Upvotes: 0

Related Questions