I have a list of projects that have overlapping dates; see example screenshot below.
In cell P1, I have the below formula which is giving me a count of overlapping projects. I have inserted some filters to only focus on specific subsets as well.
=SUMPRODUCT((F2<=FILTER($G$2:$G$8887,$N$2:$N$8887=N2,$B$2:$B$8887=B2))*(G2>=FILTER($F$2:$F$8887,$N$2:$N$8887=N2,$B$2:$B$8887=B2))))
For row 2, I can manually find the 10 overlapping projects but is there a formula that can return these 10 projects? I have quite a long list and doing it manually is not really an option.
1
It can be a solution for you:
[D2:D31]=TEXTJOIN(";",,
FILTER($A$2:$A$31,
BYROW($B$2:$C$31,
LAMBDA(a,
OR(
AND(B2>=INDEX(a,1,1),B2<=INDEX(a,1,2)),
AND(C2>=INDEX(a,1,1),C2<=INDEX(a,1,2))
)
)
)
)
)
Two projects are considered overlapped if the start date or the end date of the first project are within dates of the second project.
The advanced formula provides the clearer result:
=IFERROR(TEXTJOIN(";",,FILTER($A$2:$A$31,BYROW($A$2:$C$31,LAMBDA(a,AND(A2<>INDEX(a,1,1),OR(AND(B2>=INDEX(a,1,2),B2<=INDEX(a,1,3)),AND(C2>=INDEX(a,1,2),C2<=INDEX(a,1,3)))))))),"")
The improved formula:
=TEXTJOIN(";",TRUE,BYROW($A$2:$C$31,LAMBDA(a,IF(AND(A2<>INDEX(a,1,1),MIN(C2,INDEX(a,1,3))>=MAX(B2,INDEX(a,1,2))),INDEX(a,1,1),""))))
1
If using Microsoft 365, using similar logic to yours, counting overlapping days:
- MIN(end_date, end_date_current) – MAX(start_date, start_date_current) + 1
- > 0 indicates overlap
=LET(
prj_data, A1:INDEX(G:G, COUNTA(A:A)),
header, TAKE(prj_data, 1),
start, DROP(INDEX(prj_data, , XMATCH("Start Date", header)), 1),
end, DROP(INDEX(prj_data, , XMATCH("End Date", header)), 1),
id, DROP(INDEX(prj_data, , XMATCH("ID", header)), 1),
prj_indices, SEQUENCE(ROWS(start)),
overlapping_day_count, LAMBDA(prj_index,
MAP(
start,
end,
LAMBDA(s_, e_,
MAX(
MIN(e_, INDEX(end, prj_index)) -
MAX(s_, INDEX(start, prj_index)) + 1,
0
)
)
)
),
list_overlapping_project_ids, LAMBDA(row_index,
ARRAYTOTEXT(
FILTER(
id,
(id <> INDEX(id, row_index)) * overlapping_day_count(row_index),
""
)
)
),
MAP(prj_indices, list_overlapping_project_ids)
)