excel - returning the value of the first and last non-empty cells in a column

Hello, I would like excel to return to me the value of the first and last (in separate cells) non-empty cells in a given column. For example, I have a column with the days of a given month and numerical values assigned to them next to them. However, not every day has an assigned value, so I would like it to find the last value from a given month and the first. I hope that I wrote quite clearly

Thank you very much. I did not think that someone would do something so quickly

The problem can also be solved in Excel itself without Visual Basic.
Let us denote by with column or row range that we are analyzing. You can actually give this name to a range or in the following formulas in place of with enter the addresses of the range.
All the following formulas are array formulas , so you must approve them with Ctrl + Shift + Enter .
To read first value in a column or row range, you can use the formula:
= INDEX (z, MATCH (FALSE, ISBLANK (z), 0))

As an alternative to a column range, you can use one of the formulas:
= OFFSET (z, MATCH (FALSE, ISBLANK (z), 0) - 1,0)
= INDEX (z, MIN (IF (ISBLANK (z), "", ROW (z))) - ROW (z) +1)
The last formula can be simplified if the range starts with the first line:
= INDEX (z, MIN (IF (ISBLANK (z), "", ROW (z))))

Similarly, in the case of a row range, you can use the following formulas:
= OFFSET (z; 0, MATCH (FALSE, ISBLANK (z), 0) - 1)
= INDEX (z; MIN (IF (ISBLANK (z); "", COLUMN (z))) - COLUMN (z) +1)
The last formula can be simplified if the range starts with the first column:
= INDEX (z, MIN (IF (ISBLANK (z), "", COLUMN (z))))

To read last value in a column range, you can use the formula:
= INDEX (z, MAX (IF (ISBLANK (z), 0, ROW (z))) - ROW (z) +1)
As before, the formula can be simplified if the range starts with the first line:
= INDEX (z, MAX (IF (ISBLANK (z), 0, ROW (z))))

In the case of a row range, the analogous formula is:
= INDEX (z, MAX (IF (ISBLANK (z), 0, COLUMN (z))) - COLUMN (z) +1)
If the range starts with the first column, the formula might be shorter:
= INDEX (z, MAX (IF (ISBLANK (z), 0, COLUMN (z))))

Hello. I do not fully understand what you are writing but I have such a problem. Let me put it in simple words.
I have a 2012 price list with 1,200 articles, where in column A I have catalog numbers, in B I have names and C prices. I received a new 2013 price list which has 1226 items and also consists of three columns that I pasted in D; E; F and so:
The 2012 price list has several items that are not in the 2013 price list, while the price list has 26 more items, of which about 40 are new (somewhere in the middle).
The numbers are arranged from smallest to largest from top to bottom.
01E1007 01E1007
01E3007 01E3007
01E4007 01E4007
01E8007 01E8007
01EA125 01EA125
01M4051 01EA126
01M4061 01M4111
01M4071 01M8111
01M4081 01N4013
01M4091 01N4015
01M4101 01N4017
01M4111 01N408A
01M8051 01N4093
01M8061 01N4097
01M8071 01N8013
01M8081 01N8015
01M8091 01N8015D30
01M8101 01N8017
I need column A and D to have the same values.
When there is no number in column A in a given row that is identical to that in column D, then the row in column A should move down (with columns B and C), and if there is no value in D such as in A, the same applies to rows in column D: E and F have moved down so that there are blank lines.
01E1007 01E1007
01E3007 01E3007
01E4007 01E4007
01E8007 01E8007
01EA125 01EA125
01EA126
01EA128
01M4111
01M8111
01N4013
01N4015 01N4015
01N4017 01N4017
01N408A 01N408A
01N4093
01N4097 01N4097
01N8013 01N8013

I have only given columns A and D here, but there are also descriptions here, as I described earlier.
Can it be done easily?

Please help.

mxilopez = okonda ????

If not, then okonda create your own topic because your question has nothing to do with the main topic of the thread.
No wonder that:

okonda wrote:

"I don't quite understand what you are writing ..."

I really don't know anything about it and I need some help. it is better to comment and mock someone than help him. give a hint what the topic should be, and I will create a new one

After the dictionary of the Polish language:
Sneer - make fun of someone, mock someone, treat someone with contempt

At what point do you think I've mocked / laughed at / referred to you with contempt?
Read with understanding.

I just found out that your problem is completely other than the author's problem and you should start your own topic.
In addition, I also found that it is not strange the second fact that if you have another problem then you do not understand the answer, because unfortunately they are not the solution to your problem.

However, if you feel offended, I apologize.

As for the name of your problem (subject of the post): who and who, but you should know best what you have a problem with. But if you don't write, no one will be offended. Maybe: data synchronization in tables according to the index column or specific setting of two tables with indexes .

But I will say right away that it will probably not be an easy solution.
Write in this new thread (if possible) ultimately what you want to use it because maybe you can use other methods than "shifting" lines.

Thank you, but it seemed similar to me, and I had the impression that you "went" right away.

I need it why.

I receive a different price list every year. Each time they are, the so-called exchanged articles. Some are disappearing and some are new.
The price list comes in German or English and it is still poor (without detailed article descriptions). I have translated this price list, article by article (partially) and now I have to enter prices manually, and unfortunately there is a lot of it.

Having such a table:
once - I give copy-paste prices later, and fill in the empty ones;
two - I can do a percentage comparison as long as EVERY item changed the price
three - I have the impression that it will be useful in many other matters.

I set up a new topic and I'm waiting.

Maciej Gonet wrote:

To read last value in a column range, you can use the formula:
= INDEX (z, MAX (IF (ISBLANK (z), 0, ROW (z))) - ROW (z) +1)
As before, the formula can be simplified if the range starts with the first line:
= INDEX (z, MAX (IF (ISBLANK (z), 0, ROW (z))))

Your formula doesn't give the last value in the column, but the largest one.

Good morning,
Errare humanum est, but the formula seems to work fine to me. See example in the appendix. Please give your example and we'll check what's wrong.
Regards.

Regarding (numerical) simplifications ...

thanks. Topic finished. I have already received some great responses.