0

I want to fill a formula in Column D that computes the status duration based on the agent on Column B.

Example: I need a formula that computes On Call duration on Cell D2 for Agent John from 8AM until his next Status Break at 11AM. However, the next row to Agent John is another record for another agent. How can I compute John's duration if his next status is located on another ROW which is on ROW 5 (11AM Break)? The expected output on Cell D2 should be 3HRS since Agent John was on call from 8AM till 11AM.

Thank you

enter image description here

Improve this question
4
  • 1
    =INDEX(A3:A$7,MATCH(B2,B3:B$7,0))-A2
    – Spectral Instance
    Commented Apr 7 at 0:22
  • This is working! Thanks! I am trying to use this for a large set of data (100k + rows). Do you know other alternative to use that is light and optimized for large set data of 100k plus rows? I mean this is working perfectly but already slow when applied for too many rows like in my case.
    – Harvey
    Commented Apr 7 at 1:12
  • That would really depend on the arrangement of your data, e.g. if you knew that there would only ever be, at most, 20 rows between one status and the next for a given agent then the formula could be adjusted to scan just the next 19 rows, instead of 100k, but that is entirely dependent on the structure/sort order of your data
    – Spectral Instance
    Commented Apr 7 at 1:21
  • I got what you mean! Thank you! I will try to work on that first but so far this is all good! Thank you!
    – Harvey
    Commented Apr 7 at 2:10

1 Answer 1

Reset to default
0

Great short answer from @Spectral Instance but there is a long answer to the question of whether this can be made any faster as well.

The idea is to sort the data once then use binary search.

Formula:

=LET(colA,A1:A100000,
colB,B1:B100000,
keys,colB&TEXT(ROW(colB),"000000"),
keys1,colB&TEXT(ROW(colB)+1,"000000"),
sortkeys,SORT(keys),
sortA,SORTBY(colA,keys),
XLOOKUP(keys1,sortkeys,sortA,,1,2))

enter image description here

where column C contains the result of the original index/match for comparison. Ideally there should always be a match for each agent (an even number of entries) but if this is not the case the speeded-up formula shows spurious matches towards the end of the data (because if there are no more entries for 'L' for example, it tries with the next alphabetical entry, 'M').

enter image description here

You could adjust for this with

=LET(colA,A1:A100000,
colB,B1:B100000,
keys,colB&TEXT(ROW(colB),"000000"),
keys1,colB&TEXT(ROW(colB)+1,"000000"),
sortkeys,SORT(keys),
sortA,SORTBY(colA,keys),
matchPos,XMATCH(keys1,sortkeys,1,2),
matchB,INDEX(SORTBY(colB,keys),matchPos),
IF(matchB=colB,INDEX(sortA,matchPos),NA()))

Then with OP's data you would need to check for On call vs. Break and calculate the difference, but would probably need a larger sample of data to model it realistically.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.