I was able to adapt
Byron Wall’s answer
to How to create a dynamic table in Excel? (on Stack Overflow)
to alternate between the two columns:
E2
→ =A2
E3
→ =B2
E4
→ =IF(
INDEX(C$2:C$99, MATCH(E2,A$2:A$99), 1) > COUNTIF(E$2:E2, E2),
E2,
INDEX(A$2:A$99, MATCH(E2,A$2:A$99)+1, 1))
E5
→ =IF(
INDEX(C$2:$C99, MATCH(E2,A$2:A$99), 1) > COUNTIF(E$2:E2, E2),
E3,
INDEX(B$2:B$99, MATCH(E2,A$2:A$99)+1, 1))
Then select E4
and E5
together and drag down.
Of course you should replace 99
with the last row number of your source data
(or something higher).
For testing/demonstration purposes, I
This allows the date ranges to overlap, as above,
or like this: or to be non-overlapping, like this:
start date end date start date end date
0 15 1 10
10 25 11 20
20 35 21 30
30 45 31 40
40 55 41 50
But it does not allow A
values to have occurred on previous rows as B
values
like this: or like this:
start date end date start date end date
1 8 1 15
8 15 8 22
15 22 15 29
22 29 22 36
29 36 29 43
(i.e., the ranges must not be contiguous).
If you need to handle data like that, change E4
and E5
to:
E4
→ =IF(
INDEX(C$2:C$99, MATCH(E2,A$2:A$99), 1) >
SUMPRODUCT(--(E$2:E2 = E2), --(MOD(ROW(E$2:E2),2)=0)),
E2,
INDEX(A$2:A$99, MATCH(E2,A$2:A$99)+1, 1))
E5
→ =IF(
INDEX(C$2:C$99, MATCH(E2,A$2:A$99), 1) >
SUMPRODUCT(--(E$2:E2 = E2), --(MOD(ROW(E$2:E2),2)=0)),
E3,
INDEX(B$2:B$99, MATCH(E2,A$2:A$99)+1, 1))
I have formatted the start dates to be blue and the end dates to be yellow,
for clarity: