We help IT Professionals succeed at work.

Formula for Finding 6 Adjacent Blank Cells and Last/First Date

I need help with writing a formula on the sample file attached.

I am looking to find the following:
- First date with a value after there have been 6 blank cells in a row
- Last date with a value after there have been 6 blank cells in a row and no additional cells with any value afterwards

Sample is attached!  Thanks!
Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:

Sample is attached!

No sample was attached...


»bp


Author

Commented:
Thank you, I have it attached now
Sample.xlsx
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
To confirm the requirement I believe the desired results should be:
Formula - First date with a value after there have been 6 blank cells in a row      Customer
NA                  A      No date after the 6 blank cells
15/01/2020      B      6 blanks K to P so value from Q
10/01/2020      C      6 blanks F to K so value from L
13/01/2020      D      6 blanks I to N so value from O
NA                  E      No block of 6 blanks
      
      
Formula - Last date with a value after(should this be before?) there have been 6 blank cells in a row and no additional cells with any value afterwards      Customer
NA                  A      No block of 6 blanks
NA                  B      Value occurs after 6 blanks
03/01/2020      C      Blanks F to Q so value from E
NA                  D      No block of 6 blanks
NA                  E      Value occurs after 6 blanks

Author

Commented:
Hi Rob.

Your first assumptions are 100% accurate for the first table

And you are correct, it should be BEFORE and your assumptions are also 100% accurate
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
OK, good to know. Still don't know how it will be done by formula. I assume you have a lot more rows/columns than this so not feasible to do it manually like I have.

Are you averse to using VBA? Might be that it requires a User Defined Function (UDF) which will be VBA driven.

Author

Commented:
I would prefer not to use VBA and was hoping to use some sort of FREQUENCY or COUNT formula.  But do you feel it's truly impossible otherwise?
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
I suspect it would be possible with helper rows but that would double the size of your data. Maybe an array enabled formula but not got anything yet.

Is this a summary from some other data or is this the source data? If it was a summary from a vertical list then the helper cells might be just a single column.

Author

Commented:
This is a representation of the source data and I am ok with a helper column even if it doubles the data size

Author

Commented:
Is it possible to use a formula for the first table and then disregard the second one?  I suspect that problem is more difficult.
Finance Analyst
CERTIFIED EXPERT
Commented:
See attached.

Second table is more messy than the first because it works out the date for day 6 and then checks if there is any value after the calculated date. I suspect it could be tidied up, I will take a look when I get a chance.

There are a couple of options in the file on the two tabs:

Separate
The source data and the helper rows are separate. The helper rows can be hidden using the grouping tools on the left margin.

Combined
Each helper row is below the source data row. The helper rows can still be hidden but this took more work to create. I had to insert a row under each customer and cut and paste from the helper table into the new row. For some reason just combining the two tables and then trying to sort on customer name to group them didn't work, probably because of absolute/dynamic references getting moved by the sort. Doing for this for loads of customers would not be feasible. I can take another look at this option and resolve the sorting issue if you want.
Sample--4-.xlsx

Author

Commented:
Thank you Rob!  This was amazing and so much appreciated!
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
I realize that the question has already been answered--but you don't need helper rows to get the answer.

Last date preceded by 6 blank cells:
=IFERROR(LOOKUP(1E+40,I$1:Q$1/((C2:K2="")*(D2:L2="")*(E2:M2="")*(F2:N2="")*(G2:O2="")*(H2:P2="")*(I2:Q2<>""))),"NA")

Open in new window


Last date preceded by 6 blank cells provided that there are no subsequent dates:
=IFERROR(LOOKUP(1E+40,I$1:Q$1/((C2:K2="")*(D2:L2="")*(E2:M2="")*(F2:N2="")*(G2:O2="")*(H2:P2="")*(I2:Q2<>"")*(I$1:Q$1=MAXIFS(I$1:Q$1,I2:Q2,"<>")))),"NA")

Open in new window


These formulas test a series of six overlapping ranges for blanks. An array of results is returned: 0 if not six blanks or a 1 if there were 6 blanks. By putting this criteria in the denominator, I get an array of either 1 or error values. LOOKUP ignores the errors. The numerator is the dates from row 1. LOOKUP also has the nice property of returning the last such match if there were more than one.

The second formula adds a test to the denominator for the date in question being the last date on that row.

In fact, after some further thought you can shorten both formulas by using the reciprocal of the reciprocal of MAXIFS. The double reciprocal returns the date, unless that date was 0--in which case the IFERROR returns NA.
=IFERROR(1/(1/MAXIFS(I$1:Q$1,C2:K2,"",D2:L2,"",E2:M2,"",F2:N2,"",G2:O2,"",H2:P2,"",I2:Q2,">0")),"NA")

=IFERROR(1/(1/MAXIFS(I$1:Q$1,C2:K2,"",D2:L2,"",E2:M2,"",F2:N2,"",G2:O2,"",H2:P2,"",I2:Q2,">0",I$1:Q$1,MAXIFS(I$1:Q$1,I2:Q2,">0"))),"NA")

Open in new window


The attached workbook came from Rob Henson's post. I put my formulas off to the right and highlighted them in yellow.
LastDateAfter6Blanks.xlsx
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Hi Byundt,

I was thinking of going down similar route, looking at blocks of 6 for blanks; I didn't because I figured that the 2 weeks of data in the sample was only a small representation and decided that to go with checking blocks of 6 for say a years worth of data would make the formula extremely messy.