Hi everyone,
I am trying to find the first value in a column. Yes, that easy. Not the last, no zeros, just an empty column and at some point values start and I want the first one. I found the most complex function to find the last value. But noone searches for the first. It must be so easy and obvious that no one googles it. I found an array function for excel but in libre it doesnt work.
Any help is appreciated.
Thanks a billion and Cheers
First and last time, I am so done with this program.
This fact encourages and motivates everyone to provide an answer.
Yeah, you are right, but when frustration takes over, I do not hesitate to mention that I think handling libreoffice has many disadvantages over…other similar software
If any frustration or negative emotions are to mention here, they must be mine! FULL STOP
To look for the last entry in a column is more complicated, of course, but may be needed for acceptable and reoccurring reasons.
If someone asks how to look for the first entry, there are expectable doubts concerning the design of the sheet.
Volunteers should not be expected to be fond of answering questions all the time that would not have to be asked if the sheets were designed reasonably.
Therefore: If you have an uncommon question, try to explain the context to make the desire to help an expectable affect. And be ready then to get a suggestion not just taking the question literally.
Hi Lupp, feel free to take the ownership of frustration and negative emotion, I am happy to put that responsibility upon your shoulders. As for the design of my worksheet, it must be designed so to finally create a candlestick chart from it.
Thanks for your answer
As for the design of my worksheet, it must be designed so to finally create a candlestick chart from it.
That’s a convincing explanation, of course.
See also: XY problem - Wikipedia
Wait…
Why is the position of “the first value” in the range used for your data (feeding the chart) expected to change (not be fix)?
The image shown in the crosspost @robleyd pointed to makes me think the intended question was:
“How can I find per row the first column (left to right) actually containing something in that row?”
(To get the value from the respective cell is the next step then.)
Did I guess correctly?
I found an array function for excel but in libre it doesnt work
And if you provided us with the formula expression we maybe could tell you why it didn’t work for you and what to do about it.
Hi Lupp and erAck,
thank you for having a look into this. Finally, I solved it by the good old copy and paste. For now I will concentrate to finish my master (maybe my actual x problem over y @Lupp, even though I believe the main problem with the xy problem is that the one who has it doesnt know or believe that he/she has it). One day I will circle back to this and find out how it would have worked.
I am positively surprised by the participation in this topic of such a seemingly simple task.
Cross posted at https://www.reddit.com/r/LibreOfficeCalc/comments/oi2js4/the_easiest_function_i_cant_come_up_with/
A quick search of the interwebs provided several possible solutions:
=INDEX(range;MATCH(FALSE;ISBLANK(range);0)) entered as an array function
or
=INDEX(range;MATCH(TRUE;INDEX((range<>0;0);0))
Substitute your cell range for the string range in the sample formula.
If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. That will help other people with the same question.
In case you need clarification, edit your question (don’t use an answer) or comment the relevant answer.
[Edit] Remove extraneous comma from second formula
Hi Rob,
thanks for your fast reply and for mentioning my cross post.
As I had written, I found the array function myself and it does not work, it returns error 504.
The second formula you posted returns #NAME?. I also removed the comma from that formula, it seems off.
It does calm me however, that I am not the only one struggling to find out a formula for something seemingly so simple.
If you have any other ideas, I am happy to hear.
Cheers
As you haven’t told exactly what changes you made to the formulae it is nigh impossible to say why you got errors.
However, if you failed to follow my suggestion “Substitute your cell range for the string range” then I imagine you would get all sorts of errors.
PS - it isn’t ‘Rob’
Roble? Robleyd? I dont really get your nickname then, sorry. I also went to your profile and could use the name you mentioned there but what good is this nickname then? Thank you for your participation in the solving of this problem
Hello,
assuming you want to
A
A into cell B1
use the following formula in B1:
=OFFSET($A$1;MATCH(1;NOT(ISBLANK(A:A));0)-1;0;1;1)
Hope that helps.
Hi Opaque,
thanks for your reply. Your formula iterates over rows in a column but I was speaking of the other case, a formula that iterates over columns in a row. Maybe I formulated it wrong, sorry about that, my brain is fried by now.
I would attach a picture but this forum wont let me, but rob already mentioned my cross post, you can find a picture there
https://www.reddit.com/r/LibreOfficeCalc/comments/oi2js4/the_easiest_function_i_cant_come_up_with/
Cheers
anyways, if you found a way I am happy to hear. I copied it by hand by now, I am faster to do the same with the rest of the files. With this convoluted program working is nightmare fuel as soon as simple math requires solving. But a funny program to list some numbers.
(The attached example is based on the guess explained in my recent comment on the OriginalQuestion. If it is correct it should be explicitly accepted, and the question should be edited to be less misleading. The mentioned comment and this explanation can then be removed.)
Rather than giving formulas or general advice, I attach an example.
The most relevant formula (taken from A11 in this case, and using relative addressing) is
=MATCH("^.+$";E11:K11;0). It uses a RegularExpression in the place of the content/value to match, to do it in a specifically clear way. However, its working depends on the respective option to be enabled.
A side-effect of this formula is that it also accepts a cell returning the empty string (a probable formula result representing the idea of “no value”) as being “empty”, “without a value” or similar. This isn’t exactly the same as ISBLANK(), of course.
ask317752FirstValuePerRow.ods
Thanks! It looks promising to me.