How to identify two separate ranges of consecutively marked cells within a row

1
votes

Given the all to wide example table below, how would one go about identifying:

  1. Column H or date above as the last cell of the first group of consecutive cells with x.
  2. Column M or date above as being the first cell of the second group of consecutive x.
+---+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
|   |    A     |    B     |    C     |    D     |    E     |    F     |    G     |    H     |    I     |    J     |    K     |    L     |    M     |    N     |    O     |    P     |
+---+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| 1 | 14/01/01 | 14/01/02 | 14/01/03 | 14/01/04 | 14/01/05 | 14/01/06 | 14/01/07 | 14/01/08 | 14/01/09 | 14/01/10 | 14/01/11 | 14/01/12 | 14/01/13 | 14/01/14 | 14/01/15 | 14/01/16 |
| 2 |          |          |          | x        | x        | x        | x        | x        |          |          |          |          | x        | x        | x        |          |
+---+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+

The maximum size the gap between blocks is 356. the number of 316 in the comments is wrong due to a typo.

Formulas in use:

Identifying first overall x:

=MATCH("x",$A2:$P2,0)

Identifying last overall x:

=MATCH("x",$A2:$P2,1)

Restrictions:

No VBA, excel formulas only.
No Helper cells.
Cannot change the x to a different identifier.
One cell formula for end of first block.
One cell formula for start of second block.

Its easy when I use two different identifiers for the two groups, but using the same identifier I could only figure how to identify the very first and the very last X. I cant figure out how to identify the gap in between. I personally do not think it is possible but I have seen the people answering questions on here do some remarkable things. If you do not think it is possible please say so.

2
excelexcel-formula
Is always two groups or can it vary? – Scott Craner
I was going to start with two groups see if I could get the concept down and go from there. depending on how it works I may use it to ignore gaps for weekend. but for the time being I will leave it as two groups 1 gap of undetermined size....actually that is not totally true. the gap has to be less than a year. ie less than 360 columns...I think the biggest the gap can be is 316 column. not sure if that will matter but might as well throw it out there in case it can be used. – Forward Ed
I have some ideas. I will get on them tomorrow. – Scott Craner
no rush, it came about from another question on here and for the life of me I just could not figure out how to ID the gap. My solution was to use a different identifier for the second block. I also thought of using a start of block and end of block Identifier but that was a no go as well. I know how to generate the blocks based on given dates, but to get the date based on a block I just could not figure out. – Forward Ed
On a side note, how long did that single cell easter formula take you? I saw some of the background on that and just said what a pain in the arse! – Forward Ed

2 Answers

2
votes

These seem to survive the tests I threw at them.

In B4:B5 as CSE array¹ formulas.

=INDEX(INDEX(1:1, 0, MATCH("X", 2:2, 0)):INDEX(1:1, 0, MATCH(1E+99, 1:1)), 0, MATCH(TRUE, NOT(LEN(INDEX(2:2, 0, MATCH("X", 2:2, 0)):INDEX(2:2, 0, MATCH(1E+99, 1:1)))), 0)-1)
=INDEX(INDEX(1:1, 0, MATCH(TRUE, NOT(LEN(INDEX(2:2, 0, MATCH("X", 2:2, 0)):INDEX(2:2, 0, MATCH(1E+99, 1:1)))), 0)+MATCH("X", 2:2, 0)-1):INDEX(2:2, 0, MATCH(1E+99, 1:1)), 0, MATCH("X", INDEX(2:2, 0, MATCH(TRUE, NOT(LEN(INDEX(2:2, 0, MATCH("X", 2:2, 0)):INDEX(2:2, 0, MATCH(1E+99, 1:1)))), 0)+MATCH("X", 2:2, 0)-1):INDEX(2:2, 0, MATCH(1E+99, 1:1)), 0))

The idea is to build a sub-range of the available cells. For example, you need to find one cell less than the first blank from the first X to the last date. You would be looking for the first blank in,

INDEX(2:2, 0, MATCH("X",2:2, 0)):INDEX(2:2, 0, MATCH(1E+99,1:1 ))

Subtract 1 from the first blank after the first set, adjust for the starting position and you get the column with the last X.

last_and first

I started with another method that seemed to have difficulty when the first X was in A2 but these do not exhibit the same errors.

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

2
votes

Here's mine:

To find any groups end date:

=INDEX($A$1:$P$1,AGGREGATE(15,6,(COLUMN($A$1:$P$1))/(($A$2:$P$2="x")*($B$2:$Q$2 = "")),COLUMN(A:A)))

Where Column(A:A) is a counter, this resolve to 1 or the first. As it dragged across it will increment getting the second end date then the third and so on.

To get the second start date then:

=INDEX($A$1:$P$1,AGGREGATE(15,6,(COLUMN($A$1:$P$1)+1)/(($A$2:$P$2="")*($B$2:$Q$2 = "x")),COLUMN(B:B)))

Same with this counter as the one above.

This search for the patterns "","x" and "x","" respectively as the start and end dates.

enter image description here

To make it dynamic then:

=INDEX($A$1:INDEX(1:1,MATCH(1e99,1:1)),AGGREGATE(15,6,(COLUMN($A$1:INDEX(1:1,MATCH(1e99,1:1))))/(($A$2:INDEX(2:2,MATCH(1e99,1:1))="x")*($B$2:INDEX(2:2,MATCH(1e99,1:1)+1) = "")),COLUMN(A:A)))

And

=INDEX($A$1:INDEX(1:1,MATCH(1E+99,1:1)),AGGREGATE(15,6,(COLUMN($A$1:INDEX(1:1,MATCH(1E+99,1:1)))+1)/(($A$2:INDEX(2:2,MATCH(1E+99,1:1))="")*($B$2:INDEX(2:2,MATCH(1E+99,1:1)+1) = "x")),COLUMN(B:B)))

Upon reading @Jeeped's answer, this is probably the method he started with. Because he is correct there needs to be a blank column in A, or it will fail.