This uses basically the same logic but a slightly different approach:
To get the dates for the transitions, the values in the columns are mated vertically. When they don't match, then the next "set" is started and the date is noted.
This formula will see the difference in the "sets" and bring the date to column F using OFFSET to get the date from column A. Format column F for dates and enter this in F2 and fill down:
Formula:
=IF(OR(B1<>B2,C1<>C2,D1<>D2,E1<>E2),OFFSET(F2,0,-5,1,1),"")
The dates are now in column F. To condense this column of dates, format column G as dates and enter this array formula in G2 and fill down. This just takes the data in column F and eliminates the blank cells.
Formula:
=IFERROR(INDEX($F$2:$F$17,SMALL(IF($F$2:$F$17<>"",ROW($F$2:$F$17)-MIN(ROW($F$2:$F$17))+1),ROWS($1:1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
|
A |
B |
C |
D |
E |
F |
G |
1 |
Date |
X |
Y |
Z |
W |
SOLUTION |
|
2 |
01/07/2010 |
TRUE |
FALSE |
TRUE |
FALSE |
01/07/2010 |
01/07/2010 |
3 |
23/12/2010 |
FALSE |
FALSE |
TRUE |
FALSE |
23/12/2010 |
23/12/2010 |
4 |
05/06/2011 |
FALSE |
FALSE |
TRUE |
FALSE |
|
23/11/2011 |
5 |
09/10/2011 |
FALSE |
FALSE |
TRUE |
FALSE |
|
23/12/2012 |
6 |
23/11/2011 |
TRUE |
FALSE |
FALSE |
FALSE |
23/11/2011 |
04/04/2014 |
7 |
02/03/2012 |
TRUE |
FALSE |
FALSE |
FALSE |
|
|
8 |
05/05/2012 |
TRUE |
FALSE |
FALSE |
FALSE |
|
|
9 |
06/07/2012 |
TRUE |
FALSE |
FALSE |
FALSE |
|
|
10 |
23/12/2012 |
FALSE |
TRUE |
FALSE |
FALSE |
23/12/2012 |
|
11 |
02/05/2013 |
FALSE |
TRUE |
FALSE |
FALSE |
|
|
12 |
04/06/2013 |
FALSE |
TRUE |
FALSE |
FALSE |
|
|
13 |
02/11/2013 |
FALSE |
TRUE |
FALSE |
FALSE |
|
|
14 |
05/02/2014 |
FALSE |
TRUE |
FALSE |
FALSE |
|
|
15 |
04/04/2014 |
FALSE |
FALSE |
FALSE |
TRUE |
04/04/2014 |
|
16 |
22/07/2014 |
FALSE |
FALSE |
FALSE |
TRUE |
|
|
17 |
12/05/2015 |
FALSE |
FALSE |
FALSE |
TRUE |
|
|
Bookmarks