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

Sample is attached!

No sample was attached...

»bp

Thank you, I have it attached now
Sample.xlsx

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

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

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.

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?

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.

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

Is it possible to use a formula for the first table and then disregard the second one?  I suspect that problem is more difficult.

Thank you Rob!  This was amazing and so much appreciated!

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")

Select allOpen 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")

Select allOpen 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")

Select allOpen 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

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.