Forum Discussion
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 if any numbers in the sequence have been skipped. For instance, 2024-033, 2025-001, 2025-003, etc. There are never a set number of cases per year, so it wouldn't matter whether there should've been a 2024-034 after #33, but I would need to be able to readily see that 2025-002 was never input. If it matters, each year begins with '001' and not '000' and I'm working with Excel 2016.
9 Replies
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.
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...
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.
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.
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.
This is actually pretty easy to do with a few helper columns in Excel. First, split your case numbers into two parts, one column for the year (using =LEFT(S2,4)) and another for the sequence number (using =VALUE(RIGHT(S2,3))). Then sort your data by year and sequence number. Once sorted, you can add a formula like =IF(AND(A2=A1,B2<>B1+1),"Missing "&TEXT(B1+1,"000"),"") to quickly flag any gaps within the same year. This will show messages such as “Missing 002” when a number in the sequence is skipped. You don’t need to reinstall Excel, it’s just about setting up the right formulas to check for missing numbers automatically.
------------------------------------
Don't forget to mark as solution if my answer suits youThank you. This can only work if I take the added step of sorting the numbers first? Don't get me wrong, this is worlds better than the nothing that I had before!
