Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Feb 07, 2025
Solved

Missing slots calculation

Dear Experts,                      I have data like below:- Each Frame(Column A) has 4 corresponding slots(Column B) :- So, A6(955) has entry 3,8,13,18 , which is a complete set, and so on as ...
BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 08, 2025

    I'd suggest to add helper column to group frames, something like

    =N($I1) + --([@Frame] <> $A1)

    Next we could GROUPBY by them

    =LET(
    slots, {3,8,13,18},
    frames, GROUPBY(
        HSTACK( Table1[[#All],[Group]],Table1[[#All],[Frame]] ),
        VSTACK("Missed", Table1[Slot] ),
        LAMBDA(a, ARRAYTOTEXT( TOROW( slots / ISNA( XMATCH( slots,a ) ),2 ) ) ),
        3  ),
    dropped, DROP(frames,,1),
    FILTER( dropped, NOT( ISERROR( CHOOSECOLS(dropped,2) ) ) )
)

    Performance is not very good. Perhaps we may optimize above, but don't thing that dramatically.

    By the way, in Power Query version is the bug, very last frame is not calculated correctly. Since you prefer formula I didn't fix it.

anupambit1797's avatar
anupambit1797
Iron Contributor
Feb 08, 2025

Thanks Sergei yes that's what I meant but any legacy formula using filter function etc..?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 08, 2025

I'd suggest to add helper column to group frames, something like

=N($I1) + --([@Frame] <> $A1)

Next we could GROUPBY by them

=LET(
    slots, {3,8,13,18},
    frames, GROUPBY(
        HSTACK( Table1[[#All],[Group]],Table1[[#All],[Frame]] ),
        VSTACK("Missed", Table1[Slot] ),
        LAMBDA(a, ARRAYTOTEXT( TOROW( slots / ISNA( XMATCH( slots,a ) ),2 ) ) ),
        3  ),
    dropped, DROP(frames,,1),
    FILTER( dropped, NOT( ISERROR( CHOOSECOLS(dropped,2) ) ) )
)

Performance is not very good. Perhaps we may optimize above, but don't thing that dramatically.

By the way, in Power Query version is the bug, very last frame is not calculated correctly. Since you prefer formula I didn't fix it.

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    Feb 10, 2025

    Hi SergeiBaklan in your LET solution ,is it possible to use something other than GROUPBY?

     

    I don't have Groupby function in my version of Excel unfortunately.. :(

    Thanks again..

    Br,

    Anupam

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 10, 2025

      As variant

      =LET(
  slots, {3,8,13,18},
  Frame, Table1[Frame],
  numeration, VSTACK( 1, SCAN(1, 1*(DROP(Frame,1)<>DROP(Frame,-1)), LAMBDA(a,v,a+v) ) ),
  groups, UNIQUE(numeration),
  frames, MAP( groups, LAMBDA(g, XLOOKUP(g, numeration, Frame ) ) ),
  missed, MAP( groups, LAMBDA(g, LET(
      f, LAMBDA( FILTER(Table1[Slot], numeration = g) ),
      ARRAYTOTEXT( TOROW( slots / ISNA( XMATCH( slots, f() ) ), 2 ) )
      )
   ) ),
  both,  FILTER( HSTACK( frames, missed ), NOT( ISERROR( missed) ) ),
  VSTACK( {"Frame", "Missed"}, both  )
)

      That is without helper column

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    Feb 08, 2025

    Thanks SergeiBaklan , I just checked the last entry and yes , Power Query version has the bug, very last frame is not calculated correctly. If you can fix please PQ also, Preference is to understand both solutions,( which ever introduces less complexity)

    Br,

    Anupam

     

Resources