Max per ID (variable number of ID's)

[Hans Knudsen]

In the attached workbook I have made some comments in column AB which I hope explain the task.

 

[Hui]

Z2: =IF(COUNTIF($A$2:$A2,A2)>1,"",COUNTIFS($A$2:$A$14,A2))
copy Z2 down

 

[Hans Knudsen]

Thank you, but your formula returns exactly the same as the one I already had in column B.
What I was after was something better than my formulas in column Z, if possible.

 

[p45cal]

In attached, see cell AA2, copy down:

=IF(COUNT(d[@['#]])=1,MAX(FILTER(d[[Over-
skridelse
 5-6 min.]:[Over-
skridelse
 > 7 min.]],d[ID]=A2)),"")

Independently, see Power Query table at cell AF2. Needs refreshing if the source data changes by right-clicking the table and choosing Refresh.

 

[Hans Knudsen]

@p45cal

Thank you very much.
I like the MAX(FILTER solution and will see if I am able to understand the code in Power Query's Advanced Editor.