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.

  • mbnottingham428's avatar
    mbnottingham428
    Copper Contributor
    Oct 10, 2025

    You made this extremely helpful & somehow I have messed it up.  I copied your work, but i keep getting an error message.  No doubt some basic human error on my part, but I admit my skills with Power Query aren't quite up to par, as they should.  Since I can't attach a 'clean' copy of my sheets for reference, I've included the screenshots.

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 13, 2025

      I've already tried several times to reply but for an unknown reason my replies in the community weren't posted.

      In the screenshots i don't see the error message that's returned. However i see that there's a table named "Tabelle1" with headline "CASE NUMBER" and with a cell containing "----" (exactly 4 times "-") in sheet DATA ENTRY_ALL. Can you copy the M code from my attached file and then load your table into the Power Query Editor and paste the code into the Advanced Editor of your file. After Start -> Close and load the result should look like in my sheet.

       

Resources