CODEWITHSUNDEEP
excelexcel-formula
Forward Ed
Forward Ed

Reputation: 9874

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

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.

Upvotes: 1

Views: 79

Answers (2)

Scott Craner
Scott Craner

Reputation: 152475

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.

Upvotes: 2

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions