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
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