Forum Discussion

mbnottingham428's avatar
mbnottingham428
Copper Contributor
Oct 09, 2025

Excel formula to determine missing numbers

In column S of Sheet 1 I have a list of case numbers.  They all start with the year that the incident occurred &, followed by the incident number.  On Sheet 2, I want to be able to quickly determine ...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 10, 2025

An alternative could be Power Query which works in Excel 2013 and in newer versions. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.

mbnottingham428's avatar
mbnottingham428
Copper Contributor
Oct 10, 2025

You made this incredibly easy for me, and yet, I have still messed it up somehow, unfortunately.  Since I can't attach a sample spreadsheet, I have included screen shots.  When I copied your work into mine, I now get error messages.  My Power Query education continues...

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 11, 2025

    As variant

    =LET(
   data,   Tabelle1[Case number],
   cases,  FILTER( data, ISNUMBER( --LEFT(data,4) ) ),
   pivot,  PIVOTBY(RIGHT(cases,3), LEFT(cases,4),RIGHT(cases,3)*1, SUM,,0,,0),
   ids,    DROP(TAKE(pivot,,1),1),
   cols,   DROP(SEQUENCE(COLUMNS(pivot)), 1),
   missed, REDUCE("Missed", cols, LAMBDA(a,v,
             VSTACK(
               a,
               LET(
                 col, CHOOSECOLS(pivot,v),
                 colIds, DROP(col,1),
                 colYear, TAKE(col,1),
                 TOCOL(TAKE( IF( colIds="", colYear & "-" & ids, NA()), XMATCH("\d",colIds,3,-1) ),3)
              )
             )
          ) ),
   missed
)

    taking OliverScheurich​ file as the sample

    Power Query also work if to clean the data first, e.g. to remove errors and nulls after transforming left part to years.

Resources