Forum Discussion

anupambit1797's avatar
anupambit1797
Steel 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 shown in the ok(complete set) example.

In column J, K , I want to populate the Frames(J == SFN == Frames) which are in complete and in Column K, want to populate the slots which are missing in those corresponding Frames.

Attached is the Worksheet.

 

Thanks in Advance,

Br,

Anupam

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.

10 Replies

Sort By
  • anupambit1797's avatar
    anupambit1797
    Steel Contributor
    Feb 08, 2025

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

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      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
        Steel 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
    MVP
    Feb 08, 2025

    With Power Query that could be like

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AllSlots = {3,8,13,18},
    SelectColumns = Table.SelectColumns(Source,{"Frame", "Slot"}),

    Frames = List.Buffer( SelectColumns[Frame] ),
    n = List.Count(Frames),
    b = List.Generate(
        () => [group = 1, i=1],
        each [i] < n,
        each [ group =
            if Frames{[i]} <> Frames{[i]-1}
            then [group]+1
            else [group], i = [i]+1 ],
        each [group]
       ),
    AddGroups = Table.FromColumns( Table.ToColumns( SelectColumns ) & {b}, 
            Table.ColumnNames(SelectColumns) & {"Group"} 
     ),
    #"Grouped Rows" = Table.Group(
        AddGroups, {"Group"},
        {{"Data", each _, type table [Frame=number, Slot=number, Group=number]}}),
    AddFrame = Table.AddColumn(
        #"Grouped Rows",
        "Frame",
        each [Data][Frame]{0}),
    AddSlots = Table.AddColumn(
        AddFrame,
        "Slots",
        each [Data][Slot]),
    AddDifference = Table.AddColumn(
        AddSlots,
        "Missed",
        each List.Difference( AllSlots, [Slots] )),
    RemoveOtherColumns = Table.SelectColumns(
        AddDifference,{"Frame", "Missed"}),
    GetMissedSlots = Table.TransformColumns(
        RemoveOtherColumns,
        {"Missed", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    FilterNoMissed = Table.SelectRows(
        GetMissedSlots,
        each ([Missed] <> "")),
    SplitSlots = Table.SplitColumn(
        FilterNoMissed,
        "Missed",
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
        {"Missed.1", "Missed.2", "Missed.3"})
in
    SplitSlots

    Please check attached.

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

      Thanks SergeiBaklan and if I want to also transform this data in below format, 

      Then how to achieve it?, I tried something in K2, but unfortunately it took slots(from column B) for all Frames == 953, in actual it should be like in O and so on for other Frame.

       

      Thanks in Advance,

      Br,

      Anupam

Resources