daisypetals313

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!

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!

ASKER

Thank you, I have it attached now

Sample.xlsx

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

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

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

ASKER

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

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.

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

ASKER

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.

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.

ASKER

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

ASKER

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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:

Last date preceded by 6 blank cells provided that there are no subsequent dates:

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.

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

LastDateAfter6Blanks.xlsx

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

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

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

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.

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.

No sample was attached...

»bp