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.
2 Replies
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!
