CODEWITHSUNDEEP
excelvba
miasekk
miasekk

Reputation: 37

First and the last not empty cell in a range of multiple rows

I am struggling hard with an automatization feature for my workbook, this is why I am asking you for a help/tip.

I have a workbook in which I want to draw a work's schedule for each project(column "L"). I managed somehow to draw it accordingly to the predefined parameters, but right now another problem appeared, which unfortunately I can not solve on my own.

Every project has 12 operations to be made (OPN in column "S") and their sequence may differ. So my task is to find earliest NOT empty cell (among these 12 OPN's) and return a date value from row number 122. The second parameter which I need is the latest NOT empty cell and also return a date value from row 122. Subtracting both values will give me an amount of days needed to finish a project.

Here is the sample data: enter image description here

So, for Project 1 I need to find earliest not empty cell among OPN 1 to OPN 12 rows (rows from 165 to 176). In this case it will be cell "AP171" related to date: 19.04.2021. TO show you the last not empty cell in mentioned range I have to scroll a little bit horizontally:

enter image description here

In this case the last not empty cell in given range will be cell "BX165" related to date: 13.05.2021. At the end I would like to receive the result of subtraction of these two dates at the beginning of worksheet, in column "A" Project name and in column "B" result of subtraction.

I hope I wrote it clearly, If u have any doubts I will do my best to clarify them. I would be grateful for any kind of help :)

Upvotes: 0

Views: 69

Answers (1)

mark fitzpatrick
mark fitzpatrick

Reputation: 3320

I am not sure I fully follow you, but in Column B, to get the result of the subtraction of the date of the last non-blank column - the date of the first non-blank column, you could do:

= INDEX($T$122:$BY$122,1,MAX(COLUMN(T165:BY165)*NOT(ISBLANK(T165:BY176)))-COLUMN(T165)+1)
   - INDEX($T$122:$BY$122,1,MIN(IFERROR(COLUMN(T165:BY165)/NOT(ISBLANK(T165:BY176)),999))-COLUMN(T165)+1)

This could be optimized with Excel 365, but this formula above assumes that you have a non-Excel 365 version.

Upvotes: 2

Related Questions