I am hoping to be able to automate this data metrics and calculations. I need to identify non duplicates - and remove them from the list or just ignore in the calculations. Also, in the cases where there are more than one Step (like step B in the example), the more recent date should be kept, the other one deleted. Then every Overall Process should have Step A and Step B. As in example - only the ones in Green should be kept for calculations.
In excel i would just do custom sort (1st level Overall process(asc), 2nd - Step (desc) and then use the formula for the average time to calculate = (B Complete time on - A Created on)*24.
If i anyone has done anything similar or would have any advice it would be most appreciated.
Thanks a million in advance. Example.xlsx (9.7 KB)