Link to home
Start Free TrialLog in
Avatar of daisypetals313
daisypetals313Flag for United States of America

asked on

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!
Avatar of Bill Prew
Bill Prew

Sample is attached!

No sample was attached...


»bp


Avatar of daisypetals313

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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")

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
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.