Highlight duplicate values with a criteria within multiple select column

Sander K

@Leibel S Rows are inserted on the top

Leibel S

@Sander K @Paul Newcome

I added something to my duplicate formula to account for subsequent rows all being marked duplicate.

I also revised the formula to work per @Sander K system of rows being added on top.

1. LINE-ID: SYSTEM AUTO NUMBER
2. ROW#: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0
3. Collect Location: (multi select column) =JOIN(COLLECT(Location:Location, [Task ID]:[Task ID], [Task ID]@row, [ROW#]:[ROW#], >=[ROW#]@row), CHAR(10))
4. Location Count: =COUNTM(Location@row)

Formula for duplicate formula:

=IF(MAX(COLLECT([ROW#]:[ROW#], [Task ID]:[Task ID], [Task ID]@row)) = [ROW#]@row, 0, IF(SUM(SUMIFS([Location Count]:[Location Count], [Task ID]:[Task ID], [Task ID]@row, [ROW#]:[ROW#], >=[ROW#]@row) + SUM(COUNTM(INDEX([Collect Location]:[Collect Location], MIN(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], >[ROW#]@row, [Task ID]:[Task ID], [Task ID]@row)))) - SUMIFS([Location Count]:[Location Count], [Task ID]:[Task ID], [Task ID]@row, [ROW#]:[ROW#], >[ROW#]@row))) > COUNTM([Collect Location]@row), 1, 0))

If you need it the above formulas to work with rows being added to the bottom then the formula would be as per below:

1. LINE-ID: SYSTEM AUTO NUMBER
2. ROW#: =MATCH([Row ID]@row, [Row ID]:[Row ID], 0
3. Collect Location: (multi select column) =JOIN(COLLECT(Location:Location, [Task ID]:[Task ID], [Task ID]@row, [ROW#]:[ROW#], <=[ROW#]@row), CHAR(10))
4. Location Count: =COUNTM(Location@row)

Duplicate Formula:

=IF(MIN(COLLECT([ROW#]:[ROW#], [Task ID]:[Task ID], [Task ID]@row)) = [ROW#]@row, 0, IF(SUM(SUMIFS([Location Count]:[Location Count], [Task ID]:[Task ID], [Task ID]@row, [ROW#]:[ROW#], <=[ROW#]@row) + SUM(COUNTM(INDEX([Collect Location]:[Collect Location], MAX(COLLECT([ROW#]:[ROW#], [ROW#]:[ROW#], <[ROW#]@row, [Task ID]:[Task ID], [Task ID]@row)))) - SUMIFS([Location Count]:[Location Count], [Task ID]:[Task ID], [Task ID]@row, [ROW#]:[ROW#], <[ROW#]@row))) > COUNTM([Collect Location]@row), 1, 0))

Please let me know if this does not work.