Have no idea how to solve the following problem:
In a given sheet where column "A" is the starting date (month/day/year), "B" is the ending date and "C" is the description, any date range overlap among rows should be signaled out in column "D".
The resulting table should look like this:
| # | A | B | C | D |
|---|---|---|---|---|
| 1 | 01/01/2022 | 02/01/2022 | Task1 | Row3 |
| 2 | 01/15/2022 | 02/15/2022 | Task2 | |
| 3 | 01/29/2022 | 02/03/2022 | Task1 | Row1, Row3 |
| 4 | 01/18/2022 | 02/22/2022 | Task3 | |
| 5 | 02/02/2022 | 02/15/2022 | Task1 | Row3 |
CodePudding user response：
If the dates cross over IF(($A1<=$B$1:$B$5)*($B1>=$A$1:$A$5)
and if the Task is the same ($C1=$C$1:$C$5)
but not if the row is the same IF(ROW($A$1:$A$5)=ROW(),""
then return the Row # "Row"&ROW($A$1:$A$5)
Complete formula:
=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(($A1<=$B$1:$B$5)*($B1>=$A$1:$A$5)*($C1=$C$1:$C$5),IF(ROW($A$1:$A$5)=ROW(),"","Row"&ROW($A$1:$A$5)),"")))